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!

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

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 | Business & Productivity Software

Write down the number in cells for Example:

A1 (59) Without Brackets

A2 (50)

A3 (80)

A5 ? ANY Number

A6 ? ANY Number

A7 ? ANY Number

A8 =SUM(A1:A7) you will get total sum of above numbers on this A8 Cell which is auto sum whenever you change the value on above cell.

A1 (59) Without Brackets

A2 (50)

A3 (80)

A5 ? ANY Number

A6 ? ANY Number

A7 ? ANY Number

A8 =SUM(A1:A7) you will get total sum of above numbers on this A8 Cell which is auto sum whenever you change the value on above cell.

Feb 22, 2009 | Microsoft Business & Productivity Software

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

Hi saleem_share,

I'm not sure why you would want to use excel to create a P&L, but the same formula would apply to excel as it would for a standard P&L. In one section of the spreadsheet add all of your income accounts. Then Subtract your cost of sales from that. That would equal your Gross Profit. After that add all the rest of your Expenses, and subtract from the Gross Profit, which would give you your net Profit or Loss Example:

Description

Amount

Formula

Sales Product A

1,000.00

Sales Product B

1,000.00

Total Sales

2,000.00

SUM(C1:C2)

Cost of Sales Product A

500.00

Cost of Sales Product B

200.00

Total Cost of Sales

700.00

SUM(C5:C6)

Gross Profit

1,300.00

SUM(C4-C7)

Auto

25.00

Utilities

50.00

Payroll

300.00

Office Supplies

20.00

Total Expenses

395.00

Net Profit

905.00

SUM(C8-C14)

I hope this helps

Mark

I'm not sure why you would want to use excel to create a P&L, but the same formula would apply to excel as it would for a standard P&L. In one section of the spreadsheet add all of your income accounts. Then Subtract your cost of sales from that. That would equal your Gross Profit. After that add all the rest of your Expenses, and subtract from the Gross Profit, which would give you your net Profit or Loss Example:

Description

Amount

Formula

Sales Product A

1,000.00

Sales Product B

1,000.00

Total Sales

2,000.00

SUM(C1:C2)

Cost of Sales Product A

500.00

Cost of Sales Product B

200.00

Total Cost of Sales

700.00

SUM(C5:C6)

Gross Profit

1,300.00

SUM(C4-C7)

Auto

25.00

Utilities

50.00

Payroll

300.00

Office Supplies

20.00

Total Expenses

395.00

Net Profit

905.00

SUM(C8-C14)

I hope this helps

Mark

Jan 05, 2009 | Microsoft Business & Productivity Software

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 | Business & Productivity Software

Jan 28, 2016 | Microsoft Excel for PC

130 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!!

×