Problem with Excel formula returning #VALUE! or Incorrect result

[Buddy8]

Dear All,

I should be grateful if you would, please, help me with Excel formula that is returning either #VALUE! in a ‘Total’ cell or incorrect addition/subtraction result when any one of the cells is blank.

I have attached a sample file showing an example of what I am doing, with an explanation below of the difficulty that I am having, and for which I need your kind help.

1. From the Sample file, in Cell P90, I have the following formula =N86 which returns a value.
2. In Cell P91, I also have the following formula which returns a value or the cell is blank if there is no value to return:

‘=IF(SUMPRODUCT(--(AM6:AM85=AH86),E6:E85)=0,"",SUMPRODUCT(--(AM6:AM85=AH86),E6:E85))

3. For Cell P92, which gives the result of subtracting cell P91 from cell P90, I have the following formula: ‘=IFERROR(P90-P92,"")

4. When there are numbers in both P90 and P91, cell P92 returns a correct balance.

5. However, the following problems arise with the formula in cell P92 (the 'Total' cell):

a) If Cell P90 is blank but with a number in Cell P91 e.g. £300, Cell P92 becomes blank. Under this circumstance, I would expect P92 to show “-£300” i.e. minus £300.

b) Equally, if there is a number in Cell P90, e.g. £500 but with P91 being blank, again, P92 becomes blank instead of £500 in this circumstance (£500 minus zero) should be £500 and not zero!.

When I tried altering the formula in Cell P92, I get #VALUE!, and when I use the formula shown, I get incorrect answers. I must be doing something wrong wrong with the formula in Cell P92.

I need help, please, with the correct formula for Cell P92 to cater for the two scenarios in (a) and (b) above.

Thanks all for your help.

Buddy8

[protonLeah]

That workbook has no formulas or sample data...

[Buddy8]

Hi ProtonLeah,

Thanks for your kind response.

Yes, the workbook has no formula. I showed, on the right side of each cell, the formula that I have in the main workbook, and explained the problem that I have using such formula. I am hoping that someone would be able to examine the formulas that I have shown beside each cell to see what I am doing wrong hence Cell P92 is giving the wrong answers with respect to scenarios (a) and (b).

The reason that I have not included the actual workbook with the formulas is because Cell P91, as you can see, is a SUMPRODUCT formula which pulls together numbers from various cells to produce a single value. However, if I must provide the actual workbook with all the numbers making up the value in Cell P91, please let me know and I'll try and do that. I thought the way I presented it would help.

The problem really is that, with those formulas shown in each cell, I don't know why if there is a number in Cell P90 and P91 is blank, the formula in P92 returns a wrong answer. Also, if P90 is blank and there is a number in Cell P91, the formula in P92 again returns a wrong answer. But if there are numbers in both P90 and P91, the formula in P92 returns a correct answer! This is my dilemma with which I need help.

I hope you can, please, help with this.

Thank you.
.

Buddy8

[xlnitwit]

3. For Cell P92, which gives the result of subtracting cell P91 from cell P90, I have the following formula: ‘=IFERROR(P90-P92,"")

If one may assume that your formula is in fact =IFERROR(P90-P91,"") then you might simply amend the formula to =IFERROR(P90-N(P91),"")

[Glenn Kennedy]

When I use your formulae, it seems fine. So, you must be doing something odd.

Will you please attach a sample Excel workbook? Please don't attach an empty file. That really doesn't help at all!! Show us your formulae, in place.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.