Ok, here goes:
column F is the weight of each object. column h is the specific gravity of
each object.
In columns I,J,K,L,M,N,O,P, I want the weight of the object to be in the
column in which it's specific gravity falls.
here is the formula for each column (row 3 only)
Col (range) formula
I (<1.070)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,F3)))))))
J (1.070-1.075)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,F3,0)))))))
K (1.075-1.080)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,F3,IF(H3>1.07,0,0)))))))
L (1.080-1.085)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,F3,IF(H3>1.075,0,IF(H3>1.07,0,0)))))))
M (1.085-1.090)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,F3,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,0)))))))
N (1.090-1.095)
=IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,F3,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,0)))))))
O (1.095-1.100)
=IF(H3>1.1,0,IF(H3>1.095,F3,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,0)))))))
P (>1.100)
=IF(H3>1.1,F3,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,0)))))))
This worked great, but now I want to add 4 more categories (ie, change the
1.100 category to (1.105-1.110) and also add (1.110-1.115) , (1.115-1.120)
, and (>1.120).
For example,
=IF(H3>1.12,F3,IF(H3>1.115,0,IF(H3>1.110,0,IF(H3>1.105,0,IF(H3>1.1,0,IF(H3>1.095,0,IF(H3>1.09,0,IF(H3>1.085,0,IF(H3>1.08,0,IF(H3>1.075,0,IF(H3>1.07,0,0))))))))))
This is of course too many nested ifs.
is there a way to use vlookup or index or match? Or am I better with split
nesting?