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.
Explication of my errors shall be greatly appreciated.
Help Finding Error in Formula
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Help Finding Error in Formula
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Help Finding Error in Formula
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)
=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
Hans
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Help Finding Error in Formula
Thank you, Mr. V!
Your version works perfectly, of course!
Your version works perfectly, of course!
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Help Finding Error in Formula
or
Code: Select all
=(I2*$S$7+$S$6)*(1+$R$9)-100*(int($I2/1001)=0)
-
- PlutoniumLounger
- Posts: 15669
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Help Finding Error in Formula
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 ...
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.
He who plants a seed, plants life.
-
- Administrator
- Posts: 12635
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Help Finding Error in Formula
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
-
- PlutoniumLounger
- Posts: 15669
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Help Finding Error in Formula
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
He who plants a seed, plants life.
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Help Finding Error in Formula
Thank you for all the help, Gentlemen! (Including OMG )
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.
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.
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |