Is there a way to write a formula in excel to assign a value (1-10), to an exercise routine using multiple Intensity levels (High, Med, Low) based on a set number of minutes spent working out (10-100) and, an individuals body weight (100 lbs-250 lbs)? Any help would be greatly appreciated. Thanks Ed

SUMPRODUCT is likely a good choice. Try setting up the data you have in a table, then see which columns and rows are involved. Then if you need help setting up the formula, post back.

Shades, thanks for the response. Looks like I'm going to need all the help I can get. If you can help me with the formula I would greatly appreciate it.

Okay, let's go back to the tables of what you have.

How are you determining that something is rated 1 ? Or 10? That is, if someone weighs 150 lbs, and works out for 20 minutes at medium intensity, what kind of number do you assign? Or better, what is the method by which you will assign that number?

Lets say a 100 lb individual works out for 10 to 20 minutes at a moderate level he would be assigned a 1, however if that same individual works out for 21 to 30 minutes he would be assigned a 2, etc. however, if 200 lb individual worked out for the same period he would be assigned a 2.

Before we look at formulas, then let's clarifiy the data table even more.

One suggestion is to lay out the data such that you have values for each possible position. In Column B put the lower time limit, Column C upper time limit, and then in Columns D-J, put the weights across, and the rating in the columns themselves. Setup up one table like this, then make three of them identical (one for Intensity levels: High , Med, and Low).

So, in
B5 = Lower Minutes
C5 = Upper Minutes
D5 = 100
E5 = 125
F5 = 150
G5 = 175
H5 = 200
I5 = 225
J5 = 250

Then in

B6 = 10, C6 = 20
B7 = 20, C7 = 30

etc.

Now you can add your rating points into this table, i.e. for weights between 100-125, put the following D6 = 1, D7 = 2, etc.

Just a thought.