How to calc #'s giving totals 1-15=1, 16-25=2, 26-35=3, etc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yang-Um - 21 Jun 2006 01:43 GMT

I need a formula that can calc #'s giving totals: 1-15=1, 16-25=2, 26-35=3,
etc.

So that when I plug in say 9, in the first column, I get 1 as the total in
the next column but if I plug in 18, I get 2, etc. This is for a school
physical workout challenge with different points being awarded according to
repetitions done. The first column contains the no. of reps and the second
column contains the points awarded. The formula has to include all
reps/points increments in the range from 1-60 reps and 1-5 points.

Reply to this Message

Domenic - 21 Jun 2006 02:36 GMT

Try...

=LOOKUP(A2,{-9.99999999999999E+307,1,16,26,36,46},{0,1,2,3,4,5})

Adjust the thresholds for the first array constant {...} accordingly.

Hope this helps!

> I need a formula that can calc #'s giving totals: 1-15=1, 16-25=2, 26-35=3,
> etc.
[quoted text clipped - 5 lines]
> column contains the points awarded. The formula has to include all
> reps/points increments in the range from 1-60 reps and 1-5 points.

Reply to this Message

Max - 21 Jun 2006 06:20 GMT

Another play ..

Assume data (no. of reps) will be entered in A1 down

Put in B1:
=IF(A1="","",VLOOKUP(A1,{1,1;16,2;26,3;36,4;46,5},2))
Copy down as far as required

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message