Help Finding Error in Formula

User avatar
BobH
UraniumLounger
Posts: 9211
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Help Finding Error in Formula

Post by BobH »

Here is the formula: =IF(AND($I2>0,$I2<1000),((I2*$S$7)+$S$6)*(1+$R$9))*(I2*$S$7)+$S$6)*(1+$R$9)-100. I used formatting to distinguish parts of it for identification purposes.

I've looked at this so long that I can't see the forest for the trees. The crux of my question is the second asterisk (*, in green and underlined). Excel insists on it; I think it should be a comma but Excel says no.

Parsing the formula:
1) it is basically an IF STATEMENT whose syntax is IF TEST ..value if TRUE, ..value if FALSE.
2) the AND function is the TEST; to see if the value in cell I2 is between 0 and 1000 (it is 89; so the TEST is TRUE. Evaluating the term yields TRUE.)
3) because the TEST is TRUE, then the first string (in red) up to what I think should be a comma but is the asterisk in green and underlined should be executed.
4) if the TEST were not TRUE then the second string (in blue) after the comma should be executed.

If I insist on a comma to separate IF statement options, Excel throws an error. If I accept the asterisk that Excel inserts, I get the correct result TIMES the second string. The correct answer to the first string in RED should be "12.67". The result I get if I accept the Excel change is "60.53" indicating that it is executing both the first string and the second, IOW '12.67 time 12.67 -100' or '60.53'.

Apparently I have misinterpreted how the IF/AND combination works. :scratch:

Explication of my errors shall be greatly appreciated.

:cheers: :chocciebar: :thankyou:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help Finding Error in Formula

Post by HansV »

I think you want

=IF(AND($I2>0,$I2<1000),(I2*$S$7+$S$6)*(1+$R$9),(I2*$S$7+$S$6)*(1+$R$9)-100)

or a shorter version

=(I2*$S$7+$S$6)*(1+$R$9)-100*OR($I2<=0,$I2>=1000)
Best wishes,
Hans

User avatar
BobH
UraniumLounger
Posts: 9211
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Help Finding Error in Formula

Post by BobH »

Thank you, Mr. V!

Your version works perfectly, of course!
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

snb
4StarLounger
Posts: 547
Joined: 14 Nov 2012, 16:06

Re: Help Finding Error in Formula

Post by snb »

or

Code: Select all

=(I2*$S$7+$S$6)*(1+$R$9)-100*(int($I2/1001)=0)

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Help Finding Error in Formula

Post by ChrisGreaves »

BobH wrote:
26 Nov 2022, 22:38
I've looked at this so long that I can't see the forest for the trees. ... The crux of my question is the second asterisk . Excel insists on it; I think it should be a comma but Excel says no.
Hi Uncle; lucky for you I am now running a tree nursery!
I copied your statement from the initial post and pasted into cell A1 in the attached EXCEL2003 workbook.
Then I entered easy-to-compute-mentally values in your data cells.
Next I began assembling individual components of your complex formula. (I use strings like "ttrue" to differentiate between my interim results and the Boolean response from within a spreadsheet processor.)

No problem with the IF/AND portion, but Excel2003 burped on the "true" expression, so I removed one of the right-parentheses to effect a change from (1+$R$9)) to (1+$R$9)
That got rid of the error, but I have not checked your "false" expression, nor the resulting value in cell A1. That is left as an exercise for the reader. LATER: This means that if your parentheses balanced originally, then you might have a second extraneous parenthesis that needs to be removed, along with the first.

Please let me know if you would like a copy of "Chris's foolproof way to avoid detected and undetected errors(1) in complex spreadsheet formulas".

(1) I believe that undetected errors are significantly more worrisome than our detected errors. Too many spreadsheet developers/users do not perform rigorous manual checks on calculations. If it doesn't throw a #VALUE, #REF, or #DIV/0 error then it must be correct ... :flee:

Cheers
Your loving nephew, Chris
You do not have the required permissions to view the files attached to this post.
Last edited by ChrisGreaves on 30 Nov 2022, 16:14, edited 1 time in total.
An expensive day out: Wallet and Grimace

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Help Finding Error in Formula

Post by StuartR »

I always try to use two alternative approaches with conditional formatting to show if they differ. This works well with double-entry financial accounts
StuartR


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Help Finding Error in Formula

Post by ChrisGreaves »

StuartR wrote:
30 Nov 2022, 15:58
I always try to use two alternative approaches with conditional formatting to show if they differ. This works well with double-entry financial accounts
Absolutely.
Mt Brian Feld, our high school maths teacher, insisted that with any maths question we first calculate a solution mentally. Only then were we allowed to pick up pencils and trigonometric tables and work by formula and hand. Further, if our results did not agree to within an order of magnitude, at least one of the two calculations was in error. And then if both results agreed within an order of magnitude there remained always a low probability that both results were wrong!
I think he was trying to get across the idea that our job was to prove that neither result was in error, rather than just to provide a result.
Cheers, Chris
An expensive day out: Wallet and Grimace

User avatar
BobH
UraniumLounger
Posts: 9211
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Help Finding Error in Formula

Post by BobH »

Thank you for all the help, Gentlemen! (Including OMG :grin: )

I realize that I typed the formula incorrectly in my post (should have copied and pasted). Before posting, I did the Formulas > Evaluate process and got the results I expected EXCEPT for reasons I couldn't detect, Excel was changing a comma in the formula to an asterisk. I looked at it until I developed an inability to see it differently; thus my plea for help.

Again, thank you one and all.

:cheers: :chocciebar: :thankyou:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016