excel formula - round problem

[robcarr]

Hi,

I have identified a problem with a formula, but dont know how to fix it.

=(IF($C52-(ROUND(K$28,2))=0,(K$14-$C52)*K$42,0))*2

the formula does not take the exact figure in k28, it takes the figure rouned down to 0 decimal places, currently I have 0.52% in k28 and the formula is calcualted into K52 when it should be in K51 as this is the percentage that it fits, but because of the rounding of K28 the figure appears in the wrong cell.

ss setup - brief

in a50 - a68 i have the percentages i am checking against they go up in .5 up to a57 and 1.0 upto 68

so like

0
0.5
1
1.5
2
2.5
3
3.5
4
5
6
7
8
9
10
11
12
13
14

the formula is the same all the way down in column K except that it looks in different cells in column C,
row 28 holds the abandon rate figure.
k42 has total hours worked (143,002.71), k14 has target rate(4.00%), and k52 shows a figure of 8580.16 (which i think is wrong).

anyone got any ideas.



Hope this is of use, Rob.

 

[Zathras]

1. I think you neglected to tell us what is in cell C52, or if you did, I can't find it in your post.

2. Testing floating-point numbers for zero can caus unexpected results. Instead of
[tt]
=(IF($C52-(ROUND(K$28,2))=0....
[/tt]
try something like this
[tt]
=(IF(ABS($C52-(ROUND(K$28,2)))<0.00000000000001,....
[/tt]
3. ROUND(K$28,2) does not give "a figure rounded down to 0 decimal places" it gives a figure rounded up or down to 2 decimal places.

4. Is the value in K28 really 0.52% (which would be decimal .0052) or is it 0.52 (which would be 52%)?

 

[robcarr]

hi, c52 is the percentages I am trying to check, I said in original it was a50 to a68 it is actually c50 to c68.
k28 is really 0.52% shown as a number it shows as 0.0052

formula shows like this now

=(IF(ABS($C50-(ROUND(K$28,2)))<0.00000000000001,(K$14-$C50)*K$42,0))*2

and it still misses out the .5 increments, - if value is 0.52 it shows on 1.00% row not 0.5 row.



Hope this is of use, Rob.

 

[shetlandbob]

round(K$28,4)

Since you are formatting in percentage you need to take this into account

a value of 0.52% is a number to 4 decimal places so therefore you get:

0.52% round(value,2)= 1.00%
0.52% round(value,3)= 0.50%
0.52% round(value,4)= 0.52%

Robert Cumming

http://www.consultantengineer.co.uk

 

[robcarr]

setting to 4, does not allow values in between the .5 and 1 to show anywhwere, if value is 0.50 it shows on correct row, and if value is 0.52 it shows no where.



Hope this is of use, Rob.

 

[shetlandbob]

what are you actually trying to do? Break the formula up into smaller segments and try to debug where it is going wrong

Robert Cumming

http://www.consultantengineer.co.uk

 

[robcarr]

I am trying to get the formula to calculate the correct value into the row, depending what is in k28.

thanks

Hope this is of use, Rob.

 

[shetlandbob]

well that clears it up then!

Robert Cumming

http://www.consultantengineer.co.uk