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

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

fuzzylogic wrote:
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

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


On Fri, 15 Sep 2006 15:58:01 -0700, fuzzylogic
wrote:

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
__________________________

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?

"Richard Buttrey" wrote:

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


On Fri, 15 Sep 2006 15:58:01 -0700, fuzzylogic
wrote:

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
__________________________

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" wrote:

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

fuzzylogic wrote:
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

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

fuzzylogic wrote:
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?

"Richard Buttrey" wrote:

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


On Fri, 15 Sep 2006 15:58:01 -0700, fuzzylogic
wrote:

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
__________________________

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


On Fri, 15 Sep 2006 16:38:01 -0700, fuzzylogic
wrote:

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" wrote:

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

fuzzylogic wrote:
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
__________________________

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)-(A14)/2

do what you want?

--
HTH

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


with @tiscali.co.uk


"fuzzylogic" wrote in message
...
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 - 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" wrote:

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

fuzzylogic wrote:
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?

"Richard Buttrey" wrote:

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


On Fri, 15 Sep 2006 15:58:01 -0700, fuzzylogic
wrote:

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
__________________________

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

Pete

fuzzylogic wrote:
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" wrote:

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

fuzzylogic wrote:
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?

"Richard Buttrey" wrote:

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


On Fri, 15 Sep 2006 15:58:01 -0700, fuzzylogic
wrote:

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
__________________________