Zero values
Posted by Barry Ward on October 23, 2001 5:38 AM
I want a formula to only be applied if a value in the adjacent cell is >0.5 and likewise only want to sum those same numbers....
but even though i can get the numbers to show as zeros by formatting the number of decimal places the formula gets applied to the actual value
Posted by Aladin Akyurek on October 23, 2001 5:41 AM
Care to elaborate with sample data along with the formula that you want to use?
Aladin
Posted by Barry Ward on October 23, 2001 5:51 AM
Tailcon Male Female
CL # wt # wt
10
11
12
13
14
15
16
17
18 0 0.00 0 0.00
19 0 0.00 0 0.00
20 0 0.00 0 0.00
21 0 0.00 0 0.00
22 0 0.00 0 0.00
23 0 0.00 0 0.00
24 0 0.00 0 0.00
25 0 0.00 0 2.16
26 0 0.00 0.64 6.21
27 1.04 14.00 1.73 18.73
28 1.26 19.09 2.46 29.61
29 3.24 55.01 3.84 51.18
30 4.93 93.46 1.02 15.01
31 10.26 216.37 1.76 28.48
32 13.07 305.59 10.24 181.77
33 18.36 474.42 8.24 159.97
34 18.36 522.76 6.95 147.17
35 18.36 574.40 4.48 103.22
36 18.37 629.82 3.53 88.28
37 18.9 708.33 2.56 69.33
38 9.44 385.82 0.71 20.78
39 7.02 312.19 0.37 11.68
if you look at the first entry under female wt you'll see a value of 2.16 which is the product of the formula =(0.00074*F19^2.91)*I19 being applied to the actual value of 0.25( although this shows as 0 due to formatting) in the adjacent cell
Posted by Barry Ward on October 23, 2001 6:01 AM
In fact, what I need is for the formula to be applid to the rounded number i.e if decimal places >0.5 round up and < 0.5 round down so only whole integers are used
Posted by Aladin Akyurek on October 23, 2001 6:01 AM
Barry --
=(ROUND(F19,1)>0.5)*(0.00074*F19^2.91)*I19)
Is this what you are looking for?
& What are you summing?
Aladin
=========
Posted by Aladin Akyurek on October 23, 2001 6:07 AM
in that case, change the formula in the previous post to:
=(ROUND(F19,0)*(0.00074*F19^2.91)*I19)
Posted by Aladin Akyurek on October 23, 2001 6:09 AM
Typo Fix
=ROUND(F19,0)*(0.00074*F19^2.91)*I19)
Posted by Barry Ward on October 23, 2001 6:23 AM
Rounding
Aladin you're a star :) that's exactly what I needed
Posted by Barry Ward on October 23, 2001 6:40 AM
One more thing...............
I need each of the individual values rounded before summing them......... can I avoid using an extra row? eg
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
1.04 1
1.26 1
3.24 3
4.93 5
10.26 10
13.07 13
18.36 18
18.36 18
18.36 18
18.37 18
18.9 19
9.44 9
7.02 7
3.52 4
3.04 3
0.54 1
0.42 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0.49 0
0.51 1
0 0
0 0
0 0
0 0
0 0
151.13 149
where column 2 cell formulae are =round(column1,0)if that makes sense
Posted by Aladin Akyurek on October 23, 2001 6:51 AM
Re: One more thing...............
Barry,
Lets say that you want to sum A1:A10, where each value must be rounded before summing. You can use SUMPRODUCT instead of SUM:
=SUMPRODUCT(ROUND(A1:A10,0))
Aladin
========== 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1.04 1 1.26 1 3.24 3 4.93 5 10.26 10 13.07 13 18.36 18 18.36 18 18.36 18 18.37 18 18.9 19 9.44 9 7.02 7 3.52 4 3.04 3 0.54 1 0.42 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.49 0 0.51 1 0 0 0 0 0 0 0 0 0 0 151.13 149
Posted by Barry ward on October 23, 2001 7:34 AM
Once again perfect :) thanks (nm)
