If statements

[Guest]

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

 

[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

 

[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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

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?

 

[Guest]

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

 

[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

 

[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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Sandy Mann]

5 = 5 points

4 = 3 points
3 = 2 points
2 = 1 point
1 = 1 points

If you are on a deadline it may have past by now but would:

=MAX(1,A1/2)-(A1>4)/2

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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

 

[Guest]

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

 

[Pete_UK]

Thanks for feeding back - hope you can get it to work.

Pete

 

[Pete_UK]

Thanks for feeding back - hope you can get it to work.

Pete