If statements

F

fuzzylogic

This should be simple but I'm on a deadline. Need to write a formula that
will provide a weighted total. I think it's an IF statement something like:

IF a1=5 then * 2 (I have a table with the weights so the number 2 would be a
cell reference. Just not sure of all the commas, semi-colons and parens!
Help. Thanks!

5 = 5 points
4 = 3 points
3 = 2 points
2 = 1 point
1 = 1 points
 
P

Pete_UK

I'm not sure how your table relates to the example. But, if you do have
a table which lists the value and the weighting factor, then you can
use a VLOOKUP formula rather than a multiple-IF statement.

What are the cell references for your table?

Pete
 
R

Richard Buttrey

Am I missing something? You've shown a table which I assume to be the
weights, but a value of 5 in A1 would presumably translate to 5 points
from the table.

Assuming this is correct and the two column table named "Table" has
values in first column and weights in the second column, then

=A1 * vlookup(a1,table,2,false)

would presumably work.

HTH


This should be simple but I'm on a deadline. Need to write a formula that
will provide a weighted total. I think it's an IF statement something like:

IF a1=5 then * 2 (I have a table with the weights so the number 2 would be a
cell reference. Just not sure of all the commas, semi-colons and parens!
Help. Thanks!

5 = 5 points
4 = 3 points
3 = 2 points
2 = 1 point
1 = 1 points
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
F

fuzzylogic

Okay sounds like I've started with wrong idea. VLOOKUP must be the way to go
but I'm not clear on the how to part yet. The worksheet has 5 colums with
numbers from 1 to 5. A score of 5 is worth 2 points in the total column
which add the points across the row which is where the formula resides. Does
this help?
 
F

fuzzylogic

There are multiple rows but the first cell would be C4 (through G4) - values
range from "1" through "5"

The weighting factors for the first scenario (I'll have 3 others) is
M4 = 2 points
M5 = 2 points
M6 = 1.5 points
M7 = 1 point
M8 = 1 point

Seems like this shouldn't be so difficult. argh
 
P

Pete_UK

OK. Let's assume that you have a table in cells X1 to Y6 like the
following:

Score Points
5 2
4 2
3 1.5
2 1
1 1

and you have a "score" in A2. This formula will change that to the
relevant number of points:

=VLOOKUP(A2,$X$2:$Y$6,2,0)

This means - take the value in A2 and find an exact match (governed by
the zero at the end of the formula) in the first column (X) of the
range X2:Y6 - if a match is found, then return the value from the
second column of the range (governed by the 3rd parameter in VLOOKUP)
on the same row as the matched item.

So, if your score is 3, this formula will return 1.5.

You need to adapt this to suit your ranges.

Hope this helps.

Pete
 
R

Richard Buttrey

How does the M4, M5, M6 etc relate to the A1digit - say 5 in your
example. Can I assume that it relates to the ' 5' in M5?

Also in which case assuming the table below is in two columns, i.e.

M4 2
M5 2
etc..

in the range A0:B14

then the formula would be

=A1*VLOOKUP("M"&A1,A10:B14,2,FALSE)


HTH


There are multiple rows but the first cell would be C4 (through G4) - values
range from "1" through "5"

The weighting factors for the first scenario (I'll have 3 others) is
M4 = 2 points
M5 = 2 points
M6 = 1.5 points
M7 = 1 point
M8 = 1 point

Seems like this shouldn't be so difficult. argh
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
F

fuzzylogic

Pete - Thanks so much! I think this is the winning combination for my and
will apply this morning as it seems I now have one extra day to complete.
And thanks to all who posted suggestions. Will keep on hand for next
learning exercise. Have a good week. samer
 
Top