Question about Microsoft Excel for PC

1 Answer

Blank cell help

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!

Posted by on

  • sezhu Mar 03, 2008

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

×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Novelist:

    An expert who has written 50 answers of more than 400 characters.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

  • Expert
  • 155 Answers

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

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Excel 2007


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.

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

10 Answers

In Excel adding positive and negative numbers


Yes,


  1. Select the column. (click on top of the column it will select)
  2. Right Click and select Format Cells.
  3. Select Currency in Category then press OK
b6298df.gif Now just type your Numbers i.e. 571 for $571 and -650 for -$650 and sum using the normal way.

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

Apr 02, 2008 | Business & Productivity Software

1 Answer

Sum function


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.

Feb 22, 2009 | Microsoft Business & Productivity Software

1 Answer

HLOOKUP and IF formula netting problem


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

Feb 04, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

IF Formula


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.

Jan 10, 2009 | Microsoft Office Standard for PC

2 Answers

How to do profit and loss a/c in ms excel


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

Jan 05, 2009 | Microsoft Business & Productivity Software

2 Answers

Formular to show number and cell references


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.

Mar 07, 2008 | Microsoft Excel for PC

1 Answer

=if


=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

Feb 29, 2008 | Microsoft Excel for PC

1 Answer

Excel formula


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 !

Jan 16, 2008 | Microsoft Excel for PC

1 Answer

Using if and then to add values to a cell


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

Dec 09, 2007 | Business & Productivity Software

Not finding what you are looking for?
Microsoft Excel for PC Logo

130 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...