Excel Formula not performing

  Abel 14:22 25 Jun 2007
Locked

The following Excel formula in is in cell AH19
=IF(ISBLANK(AA19),"",IF(AA19-AF19-AG19=0,0,IF(AE19="Taxd.t.d?","",AA19-AF19-AG19))) and fails to return an answer. AA19 is £420, AF19 and AG19 each have a zero value of £0.00, which of course should return the answer of £420. Can anyone suggest a reason for this? Or, in fact, suggest a more efficient way of expressing the formula.

Abel

  alfredgerald 16:23 25 Jun 2007

The formula works ok on Excel 2000. Although not as currency. Just gives 420. Dont know what to suggest. Sorry

  daba 19:04 25 Jun 2007

Excel 97 here,

All works OK - even if cells formatted as currency, even if decimal places set to 0, etc. etc.

Explain "fails to return an answer" in detail, any error text.

I would suggest you have "Taxd.t.d?" in AE19, that causes AH19 to be blanked.

  Abel 19:23 25 Jun 2007

Many thanks for responding alfredgerald and daba. First of all the cell in which the answer should appear remains totally blank. However, at your suggestion daba, I removed that part of the formula with "Tax d.t.d." in it and the cell returns the correct answer. Obviously, I have got to look at what the reason are. I shall do this tomorrow and report back.

Abel

  daba 19:46 25 Jun 2007

=IF(ISBLANK(AA19),"",IF(AA19-AF19-AG19=0,0,IF(AE19="Taxd.t.d?","",AA19-AF19-AG19)))

equates to

IF ISBLANK(AA19) THEN "", -False
__ELSE IF AA19-AF19-AG19 = 0 THEN 0, -False
_____ELSE IF AE19="Taxd.t.d?" THEN "", -True
_________ELSE AA19-AF19-AG19 -irrelevant

Its the third line that is blanking the cell

  Abel 19:53 25 Jun 2007

Thanks daba! I'll attempt to understand the reason why and amend the formula. Certainly, the way you've laid it out helps to concentrate the mind.

Abel

  Abel 21:12 25 Jun 2007

daba, I'm having trouble understanding how line three of the equation is TRUE, when "Tax d.t.d." doesn't appear in cell AE19. Would you elaborate for me please?

By the way, is there something up with the PCA site this-evening? It seems to be responding very slowly. That is of course, if it isn't my machine or ISP.

Abel

  daba 21:36 25 Jun 2007

Are you saying that AE19 is empty, or says something else?

You could select AE19 and delete the contents to prove the formula works, the numeric answer should appear in AH19, certainly does on my machine.

Try changing this part:-
IF(AE19="Taxd.t.d?","",AA19-AF19-AG19)

To this :-
IF(AE19="Taxd.t.d?",AE19,AA19-AF19-AG19)

If it shows "Taxd.t.d?" in AH19, then that is what you have in AE19, or it will do the maths and show the numeric result.

  daba 21:56 25 Jun 2007

Formula can be simplified by making the test of AE19 the primary test, the test if AA19 is blank, the second, you don't need to test for AA19-AF19-AG19 = 0, since if true, you are putting zero in the result. Hence:-

=IF(AE19="Taxd.t.d?","",IF(ISBLANK(AA19),"",AA19-AF19-AG19))

Does exactly the same as your original formula but use only 2 nested IFs

  Abel 22:18 25 Jun 2007

You're a better man than I daba (if you're female my apologies). I'll try hard to analyse and understand what you've said, in an attempt to solve my problem. My thanks for all you effort. I'll get back to the forum some time tomorrow.

Abel

  daba 00:34 27 Jun 2007

I've just re-read my explanation, and it didn't seem that clear to me either, and I wrote it !

If it helps:-

Second IF in your formula :- "IF(AA19-AF19-AG19=0,0....." says "If AA19-AF19-AG19 = 0" then put 0 as the result.

Third part of your formula :- "IF(AE19="Taxd.t.d?","",AA19-AF19-AG19) will also put 0 as a result if AA19-AF19-AG19 is zero (assuming that text isn't in AE19).

All I did was re-arrange the formula to use one test to put the result anyway, regardless of whether the math equates to zero, which is what the second IF does.

In English, the formula says:-

if "Taxd.t.d?" is in AE19, clear the result. Otherwise, if nothing has been entered into AA19, clear the result, otherwise put AA19-AF19-AG19

Now the thought occurs that you are using ISBLANK to test if a NUMBER (in your case formatted as currency), has been entered into AA19 (it is only relevant to do maths with numbers).

ISBLANK only works if the cell has nothing (not 0) in it, so this idea would fail if anything else occupied it. Therefore it would be better to use the ISNUMBER function, but that would need the true,false statements in the IF to be reversed, because ISNUMBER has the opposite "sense" to ISBLANK ... e.g. :-

=IF(AE19="Taxd.t.d?","",IF(ISNUMBER(AA19),AA19-AF19-AG19,""))

Now I believe that arrangement reads better, and actually "says" more like what you want the formula to achieve. I'm sure there'd be a way to use AND in the formula somewhere, since you are looking at 2 conditions to calculate the result, but I don't see much point, the nested IF construction is probably easier to understand.

There is confusion though about what could be in AE19. Your original formula looks for "Taxd.t.d?", but you have mentioned "Tax d.t.d." (posts 4 & 7). It looks like some sort of a question which might have an answer Yes or No (or similar). Is there some sort of formula in AE19 which returns "Taxd.t.d?" or "Tax d.t.d." or what have you got in there ?

Hope my belated explanations better understood.

"dave"

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires