Adding one to two figures with moving decimals
Loadmaster
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.
p45cal
I'm just wondering if this is way too complicated..
The differences between the values in U4:AC4 are all 5.4 - the same as
the value in U4.
The differences between the values U3:AC3 are all 7.3 - the same as the
value in U3.. EXCEPT for the value Y3 which is only 7.2 above the value
to its left.. which just so happens to be the value you cite in:
> Had column Y3 have read 36.5 vice 36.4 you would have added 3.7
point.
So to keep things simple, couldn't you just use the value in the 1000lb
column as the value per 1000lbs? That would mean you could do simple
arithmetic like:
=($R3/1000)*$U3
in cell AF3, as a starter. It also means you don't need any values
except for those in the 1000lb column.
Now I note that you round 50 up to 100. I'd guess you want to round all
values UP to the nearest 100, even 101 becomes 200..?
If so, adjust the formula to:
=(CEILING($R3,100)/1000)*$U3
Finally, you can display the result with a different number of decimal
places to suit, or actually round the result within the formula.
Come back if I'm not on the right track.
--
p45cal
*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=157072
[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Loadmaster
Luke M
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!*