Cells U2:AC2 have 1000-9000 lbs. Cells S3:S14 have Compartments B thru M
Cells U3:AC3 have the following figures corresponding the weights above.
7.3 for 1000 lb in compartment B. 14.6 for 2000 in compartment B. Here are
the rest of the figures for Compartment B; 21.9, 29.2, 36.4, 43.7, 51.0, 58.3
and 65.6. The next row of numbers for Compartment C in row 4 are as follows;
5.4, 10.8, 16.2, 21.6, 27.0, 32.5, 37.9, 43.3 and 48.7. Upon entering a
figure of 1500 in compartment R3, cell AF3 should add to read 7.3 from the
1000 column plus 3.6 from the 5000 column moving the decimal one place over
to the left. Had column Y3 have read 36.5 vice 36.4 you would have added 3.7
vice 3.6 to round it up. If I had put 15000 vice 1500 in cell R3, cell AF3
would read 73.0 plus 36.4 for a total of 109.4 If I had 50 lb in R4 it would
be rounded up to 100 lbs AF4 would read 0.5 from the 5.4 from the 1000 lbs
in compartment C. 3900 in cell R4 would read 16.2 plus 4.9 which is the 48.7
rounded up for the 900 lbs left for a total of 21.1 in cell AF4.

Instead of using the complicated table, why not just use the rate (since it
appears constant, and you make the assumption that 500 is equal to 5000/10).
Thus, formula in AF3 is:

=R3*(7.3/1000)

Or, if you want a single column:
=R3*(U3/U$2)

You may need to play around with the number of decimals shown, or the ROUND
function, but this looks like an easier way, and I was able to get it to work
for all of your examples.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Loadmaster" wrote:

Cells U2:AC2 have 1000-9000 lbs. Cells S3:S14 have Compartments B thru M
Cells U3:AC3 have the following figures corresponding the weights above.
7.3 for 1000 lb in compartment B. 14.6 for 2000 in compartment B. Here are
the rest of the figures for Compartment B; 21.9, 29.2, 36.4, 43.7, 51.0, 58.3
and 65.6. The next row of numbers for Compartment C in row 4 are as follows;
5.4, 10.8, 16.2, 21.6, 27.0, 32.5, 37.9, 43.3 and 48.7. Upon entering a
figure of 1500 in compartment R3, cell AF3 should add to read 7.3 from the
1000 column plus 3.6 from the 5000 column moving the decimal one place over
to the left. Had column Y3 have read 36.5 vice 36.4 you would have added 3.7
vice 3.6 to round it up. If I had put 15000 vice 1500 in cell R3, cell AF3
would read 73.0 plus 36.4 for a total of 109.4 If I had 50 lb in R4 it would
be rounded up to 100 lbs AF4 would read 0.5 from the 5.4 from the 1000 lbs
in compartment C. 3900 in cell R4 would read 16.2 plus 4.9 which is the 48.7
rounded up for the 900 lbs left for a total of 21.1 in cell AF4.