Same Sum Formula Returns Different Results
Hi Guys,
Please see the attached WorkBook.
I find this very strange
In Column E the Sum Formula returns the correct result, but using that same Formula it returns a wrong result in Column B. And I cannot figure out why this is so.
What am I doing wrong?
b9=ak1
e9=an1
The formule in AK1 is 100% equal to the formula in AN1?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi oeldere,
Thank you for responding.
You meanb9=ak1
e9=an1Your observation is correct. AK1 is 100% equal to the formula in AN1. Then why does B11 show 13 instead of 65 like it shows in E11?b11=ak1
e11=an1
You have mismatched brackets in the formula in AK1. The following returns 65 as the result.
Please Login or Register to view this content.
Thank you so much Cutter!
I am so tired that I just cannot see the wood from the trees, so to speak. I have assumed that if it works in AN1, it should also do so in AK1. But you have spotted my mistake which I will have a look into when I feel "fresher".
I also just had a gut feel that you were going to solve this one for me.
Once again, Thank You.
You're welcome. Thanks for the 'star tap'.
You know, you could simplify things:
The first section MAY be simplified from:
=IF(RIGHT(A15,2)="1S",B15,IF(RIGHT(A15,2)="2S",B15*2,IF(RIGHT(A15,3)="2SN",B15*2,IF(RIGHT(A15,3)="2SW",B15*2,IF(RIGHT(A15,2)="3S",B15*3,IF(RIGHT(A15,3)="3SN",B15*3))))))
to:
=MID(A15,11,1)*B15
depending on the consistency of your data
similar changes to the rest of the sections, if so
Last edited by Cutter; 08-05-2012 at 09:21 AM. Reason: Added thanks
Hello Cutter,
Thank you for the feedback. Unfortunately my Data is too inconsistent to use the MID function.
Keep up the good work!
OK, how about the following which just finds the number preceding the "S" in the last 3 characters:
=MID(RIGHT(A15,3),FIND("S",RIGHT(A15,3))-1,1)*B15
to replace
=IF(RIGHT(A15,2)="1S",B15,IF(RIGHT(A15,2)="2S",B15*2,IF(RIGHT(A15,3)="2SN",B15*2,IF(RIGHT(A15,3)="2SW",B15*2,IF(RIGHT(A15,2)="3S",B15*3,IF(RIGHT(A15,3)="3SN",B15*3))))))
Hello Cutter,
Wow, I really like that. I never knew that one could use a combination of MID & RIGHT.
I have tried it, but it returns a #VALUE# error if one of the lines is empty. Then I tried;
which also does not work.Please Login or Register to view this content.
Thank you for all your time and efforts.
This alternative came to me while I was cutting the grass (don't ask, I don't know why):
=LEFT(TRIM(RIGHT(A15,3)))*B15
instead of
=MID(RIGHT(A15,3),FIND("S",RIGHT(A15,3))-1,1)*B15
On the formula in your last post you would get #VALUE error because you are adding text (the "" result is text). You can avoid that particular scenario by placing the components within a SUM(), which ignores text, OR replace the "" returns with 0.
Try this:
=IF(SUM(B15:B23)=0,"",
IF(A15<>"",LEFT(TRIM(RIGHT(A15,3)))*B15,0)
+IF(A17<>"",LEFT(TRIM(RIGHT(A17,3)))*B17,0)
+IF(A19<>"",LEFT(TRIM(RIGHT(A19,3)))*B19,0)
+IF(A21<>"",LEFT(TRIM(RIGHT(A21,3)))*B21,0)
+IF(A23<>"",LEFT(TRIM(RIGHT(A23,3)))*B23,0))
You could even do away with the opening IF() which would yield a 0 result instead of a "" result if that's acceptable.
Last edited by Cutter; 08-06-2012 at 03:41 PM. Reason: Added explanation & alternative
Hello Cutter,
Absolutely awesome!!! Brilliant and outstandingly perfect!
Words fail me and, I cannot thank you enough for your valued persistence and contribution.
It is working PERFECTLY, empty rows or not!
I have also added a Gold Star next to your name on my wall of Fame.
I also owe you a Coke and a Bun...LOL
Wow, that's got to the best thank you I've ever received. And a 'star tap' to go with it, too. Thanks.
And I'll take you up on that Coke and a Bun.....if my sister ever convinces me to visit her in Jo'burg.
Anyway, you're very welcome...glad I could help you get rid of that monster formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks