I am using this function for Cell E6:

=SUM((B6+D6)-(B5+C6))

It works great but if B6 is blank it returns an erroneous negative number for E6 since it assumes B6=0.

How can I modify the formula to force a blank E6 if B6 is bank?

THANKS!

Ad

You can use an IF statement:**IF**(**logical_test**,**value_if_true**,value_if_false)

In your case, this would be:

=IF(B6<>"",SUM((B6+D6)-(B5+C6)),"")

This works as follows:

IF(B6<>"", : If B6 is not equal to nothing, ie. it equals something

SUM((B6+D6)-(B5+C6)), : Set the value as per the equation

"") : Otherwise set the value to nothing.

Of course, this only checks that there is a value in B6. Depending on that value (and that of the other cells) you could still get a negative result. The way to avoid this would be to use the following:

=IF((SUM((B6+D6)-(B5+C6))>0),SUM((B6+D6)-(B5+C6)),"")

A bit long winded, but basically it checks the result of the sum is greater than 0. If it is it displays it, if not it leaves the cell blank.

Hope this helps,

Matt

Posted on Mar 03, 2008

Ad

Hi,

a 6ya Technician can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repair professionals here in the US.

click here to Talk to a Technician (only for users in the US for now) and get all the help you need.

Goodluck!

Posted on Jan 02, 2017

Ad

The IRR function is provided by Excel so you can calculate an
internal rate of return for a series of values. The IRR is the interest
rate accrued on an investment
consisting of payments and income that occur at the same regular
periods. In the values provided to the function, you enter payments you
make as negative values and income you receive as positive values.

For instance, let's say you are investing in your daughter's business, and she will make payments back to you annually over the course of four years. You are planning to invest $50,000, and you expect to receive $10,000 in the first year, $17,500 in the second year, $25,000 in the third, and $30,000 in the fourth.

Since the $50,000 is money you are paying out, it is entered in Excel as a negative value. The other values are entered as positive values. For instance, you could enter –50000 in cell D4, 10000 in cell D5, 17500 in cell D6, 25000 in cell D7, and 30000 in cell D8. To calculate the internal rate of return, you would use the following formula:

=IRR(D4:D8)

The function returns an IRR of 19.49%.

The ranges you use with the IRR function must include at least one payment and one receipt. If you get a #NUM error, and you have included payments and receipts in the range, then Excel needs more information to calculate the IRR. Specifically, you need to provide a "starting guess" for Excel to work with. For example:

=IRR(D4:D8, -5%)

This usage means that the IRR function starts calculating at –5%, and then recursively attempts to resolve the IRR based on the values in the range.

For instance, let's say you are investing in your daughter's business, and she will make payments back to you annually over the course of four years. You are planning to invest $50,000, and you expect to receive $10,000 in the first year, $17,500 in the second year, $25,000 in the third, and $30,000 in the fourth.

Since the $50,000 is money you are paying out, it is entered in Excel as a negative value. The other values are entered as positive values. For instance, you could enter –50000 in cell D4, 10000 in cell D5, 17500 in cell D6, 25000 in cell D7, and 30000 in cell D8. To calculate the internal rate of return, you would use the following formula:

=IRR(D4:D8)

The function returns an IRR of 19.49%.

The ranges you use with the IRR function must include at least one payment and one receipt. If you get a #NUM error, and you have included payments and receipts in the range, then Excel needs more information to calculate the IRR. Specifically, you need to provide a "starting guess" for Excel to work with. For example:

=IRR(D4:D8, -5%)

This usage means that the IRR function starts calculating at –5%, and then recursively attempts to resolve the IRR based on the values in the range.

Jun 09, 2010 | Microsoft Office Professional 2007 Full...

Yes,

That is Select the Cells which you want to SUM and Press**Alt+=**

This will bring the total just below the selected cells.

Thanks

Iqbal

- Select the column. (click on top of the column it will select)
- Right Click and select Format Cells.
- Select
**Currency**in**Category**then press**OK**

That is Select the Cells which you want to SUM and Press

This will bring the total just below the selected cells.

Thanks

Iqbal

Apr 02, 2008 | Computers & Internet

CW Keys 5C01 updated 06/10/09 10:20

00= BC B6 BB DD 97 AD 11 63

01= 42 B7 84 3E BE 8C 4E 65

TW Keys 5A01 updated 06/10/09 10:20

00= C2 B5 BE 41 0D 9B C5 7C

01= 6D EA 8E 97 6A AA DA 71

NTL Keys 5401 updated 06/10/09 10:20

00= A0 51 3B 27 17 7D D0 28

01= F0 CF C3 01 F1 2D AB B8

00= BC B6 BB DD 97 AD 11 63

01= 42 B7 84 3E BE 8C 4E 65

TW Keys 5A01 updated 06/10/09 10:20

00= C2 B5 BE 41 0D 9B C5 7C

01= 6D EA 8E 97 6A AA DA 71

NTL Keys 5401 updated 06/10/09 10:20

00= A0 51 3B 27 17 7D D0 28

01= F0 CF C3 01 F1 2D AB B8

Sep 20, 2009 | StarTech.com StarView SV211K 2-port KVM...

The value of C7cannot be found in the range - Hence the error.

Feb 04, 2009 | Microsoft Office Excel 2003 for PC

You can use this formula

=IF(A2<=100,"Within budget","Over budget")

Which means

If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)

or you and try something like this

=IF(A2=100,SUM(B5:B15),"")

which means

If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

I got these examples from the help within Exel they give several more examples and more expaination.

=IF(A2<=100,"Within budget","Over budget")

Which means

If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)

or you and try something like this

=IF(A2=100,SUM(B5:B15),"")

which means

If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

I got these examples from the help within Exel they give several more examples and more expaination.

Jan 10, 2009 | Microsoft Office Standard for PC

- For starview super box
- Press MENU
- Press 1570
- Go to NAGRAVISION and press OK
- Go to Key Number with the arrows on your remote, press two times to the right. Now you should have 00 again
- Go Key Data and enter this code: B0 B8 4F 04 E6 5F 87 80 and press OK.
- Go back to Key Number and press arrow to change to 01
- Than go back to Key Data and enter code: A3 EB EC 07 06 49 D2 EB and press OK.
- Last step: press EXIT 3 times. Your channels should be now restored.
- Ps. Make sure to install all numbers and letters of the code don’t over look any of them for this will cause problems reinstalling your channels…. try wit eurovox

Dec 16, 2008 | StarTech.com StarView SV211K 2-port KVM...

The formula for A1 is: =SUM(B4,B5)

A2 is: =SUM(C4,C5)

A3 is: =SUM(A1:A2)

To test this, put 25 in cell B4, 25 in cell B5, then 30 in cell C4, and 30 in cell C5.....the result in cell A3 is 110.

Hope this helps.

A2 is: =SUM(C4,C5)

A3 is: =SUM(A1:A2)

To test this, put 25 in cell B4, 25 in cell B5, then 30 in cell C4, and 30 in cell C5.....the result in cell A3 is 110.

Hope this helps.

Mar 07, 2008 | Microsoft Excel for PC

=IF(C5>B5,0,B5*2)

this means if it is true that C5>B5 then the cell = 0,

but if it is not true, means C5< or = B5, the cell will be B5*2

You can change the numbers as you need.

To use function easily, you can click "Insert" --> function --> then choose in the "select a function" -> OK.

Then follow the instructions. Very clear to follow

this means if it is true that C5>B5 then the cell = 0,

but if it is not true, means C5< or = B5, the cell will be B5*2

You can change the numbers as you need.

To use function easily, you can click "Insert" --> function --> then choose in the "select a function" -> OK.

Then follow the instructions. Very clear to follow

Feb 29, 2008 | Microsoft Excel for PC

Yes, there is a function in MS Excel called "sumproduct" which multiplies the componenets in an array or arrays, then totals the result, eg :

=SUMPRODUCT(D4:D20,B4:B20) ...Job done...Enjoy !

=SUMPRODUCT(D4:D20,B4:B20) ...Job done...Enjoy !

Jan 16, 2008 | Microsoft Excel for PC

Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Computers & Internet

Jan 28, 2016 | Microsoft Excel for PC

Dec 12, 2013 | Microsoft Excel for PC

Jul 23, 2013 | Microsoft Excel for PC

142 people viewed this question

Usually answered in minutes!

Thanks Matt!

It works if the E6 result is positive but not if it is negative. However, you set me on the right path. I modified it to:

=IF(SUM(B6>0),SUM((B6+D6)-(B5+C6)),"")

AND IT WORKS GREAT.

Thanks So Much!!

×