Results 1 to 12 of 12

20020227, 06:39 #1
 Join Date
 Jan 2001
 Location
 Brisbane, Australia
 Posts
 245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculation Funnies? (MS Excel/97)
The attached file has a problem that I seek help with.
The calculations are not correct and I am unable to work out why.
Can anyone offer advice please?
TIA, Leigh

20020227, 07:38 #2
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
I am not sure in what way the calculation is incorrect.
It seem to be doing what it should be. ie
with your figures in B15:C15 the max is greater than 0.46 and your formula gives 0.25
delete them and the max is not greater than 0.46 and the formula gives 0.5
What were you expecting to get?

20020227, 07:42 #3
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
The formula in F7 should be
=IF(MAX(C7:C10>=0.46),"0.25","0.5")
because the value in C8 is PRECISELY 0.46. HTHGrüße

20020227, 14:49 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
I see at least three things wrong with your formula:
1 The first right parenthesis is in the wrong place. The correct place is:
<pre>=IF(MAX(C7:C10)>0.46,"0.25","0.5")
</pre>
2 Your formula does not take binary conversion rounding errors into account. So, if you format cell C8 to a number and increase the number of decimal places displayed to 15, you will see that the actual value in that cell si 0.460000000000008. That is greater than 0.46 and therefore will cause the formula in F7 to return ".25" even though it looks like it should return ".5". You could correct this like this:
<pre>=IF(ROUND(MAX(C7:C10),2)>0.46,"0.25","0.5")
</pre>
3 Your IF statement is returning a string value, not a number value. That is fine if that is what you want, but if you want to use this cell in other calculations, the formula should read:
<pre>=IF(ROUND(MAX(C7:C10),2)>0.46,0.25,0.5)
</pre>
Legare Coleman

20020227, 20:01 #5
 Join Date
 Jan 2001
 Location
 Brisbane, Australia
 Posts
 245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
Thanks for everyones' replies.
I rushed to create the workbook and did put the closing bracket for the MAX function in the wrong place. (The PC I sent from did not have MS Excel to check before sending. What about Internet Explorer for reading Office files, I hear you ask... I forgot!)
I had heard about binary rounding but had not investigated it, but Legare's response hit the nail right on the head, again.
I am grateful, Leigh

20020228, 07:00 #6
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
Curiouser and Curiouser!!
The formula worked just fine as it was for me! Until I rewrote it elsewhere!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
If you look at the attached picture you will see that after deleting B15:C15, F7 gave the result 0.5 (as a number) but when I retyped the formula in F8 I got 0.25 as text!
Copying or dragging the formula still gives me the correct (but wrong?? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> ) result <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Think that I will need stronger tablets now LOL <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

20020228, 14:21 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
Are you sure that result is in F7 is a number and not text? On the original sheet, that cell is formatted to align right so text would look like a number. However, when I enter this formula in another cell:
<pre>=ISTEXT(F7)
</pre>
The result is True.
Can't tell why you got the different results without having the workbook to look at.Legare Coleman

20020228, 16:54 #8
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
I did not think to look a cell alingment <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
The workbook was just as I opend it with Exporer, but I tried copying the cells over to excel proper and got the same effect.
copying the cell left it the same, but copying the formula from the edit bar and pasting it into another cell broke the thing!
Peter

20020228, 19:48 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
Copying and Pasting takes the format along with the value unless you do a Paste Special/Value.
Legare Coleman

20020228, 21:21 #10
 Join Date
 Jan 2001
 Location
 Brisbane, Australia
 Posts
 245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
The original MS Excel workbook (not the sample file posted by me) was actually intended to return text as both the True and False arguments. It relies on values being met to return the 'text' .
The solution came when Legare advised that the binary conversion could be allowed for by using the ROUND function. This did solve my particular case, mainly because the results in C7:C10 were not simple values, but the calculations returned from the values in B16:C18.
Many thanks for your input, Leigh

20020304, 20:22 #11
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
I know it 's a bit late in the day, but did you also check you had 'Precision as displayed' checked on the toolsoptionsCalculations tab.
I can feel the flames coming already, but it does exactly as it says and gives 'true to view' answers.
Alan

20020304, 21:00 #12
 Join Date
 Jan 2001
 Location
 Brisbane, Australia
 Posts
 245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculation Funnies? (MS Excel/97)
Thanks Alan
I did not have that option enabled as there is a need to use the figures as precisely as possible. In this case, that meant that a value of 0.46 had to be considered other than the MS Excel calculated value of 0.460000000...08
My problem was answered by Legare (and Bat17 raised an issue with the same formula) although you make a good point of which others need to be aware.
Leigh