Calculation Funnies? (MS Excel/97)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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. HTH

Reply | Quote

I see at least three things wrong with your formula:

1- The first right parenthesis is in the wrong place. The correct place is:

=IF(MAX(C7:C10)>0.46,"0.25","0.5")

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:

=IF(ROUND(MAX(C7:C10),2)>0.46,"0.25","0.5")

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:

=IF(ROUND(MAX(C7:C10),2)>0.46,0.25,0.5)

Reply | Quote

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

Reply | Quote

Curiouser and Curiouser!!

The formula worked just fine as it was for me! Until I re-wrote it elsewhere!!
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?? ) result

Think that I will need stronger tablets now LOL

Reply | Quote

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:

=ISTEXT(F7)

The result is True.

Can’t tell why you got the different results without having the workbook to look at.

Reply | Quote

I did not think to look a cell alingment

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

Reply | Quote

Copying and Pasting takes the format along with the value unless you do a Paste Special/Value.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote