at a loss for a formula

[ExcelDummy]

I am trying to create a points sheet. What I have not been able to do is figure out a formula that will work.

In one cell I need to enter a number that would be a 1, 2, 3, 4 or 5. If I enter the number 1, it will delegate 30 points in a given cell, if I enter the number 2, it will delegate 40 points and so on.

 

[sen_edp]

Hello, and welcome

I hope this example suits your need

Book1
	A	B	C	D
1	1	30
2	2	40
3	3	50
4	4	60
5	5	70
Sheet1

_________________
Best Regards,<font color="black"><font size=+1><font color="blue">A<font color="red">ndrea<font color="blue">S</font color="red"><font size=+1><font size=1><font color="red">using xl2000
This message was edited by sen_edp on 2002-09-14 14:58

 

[Dave Patton]

On 2002-09-14 14:48, ExcelDummy wrote:
I am trying to create a points sheet. What I have not been able to do is figure out a formula that will work.

In one cell I need to enter a number that would be a 1, 2, 3, 4 or 5. If I enter the number 1, it will delegate 30 points in a given cell, if I enter the number 2, it will delegate 40 points and so on.

try

=IF(OR(A1={1,2,3,4,5}),A1*10+20,"")

 

[Aladin Akyurek]

=(A1>0)*20+A1*10

if the regularity 1->30, 2->40 "and so on" holds.

 

[ExcelDummy]

Thanks for the Welcome, you guys are very impressive!!!!

I got the formula to work, but I would like to add a "W" for a different point total. I couldn't get the formula to work, so my next question is, am I stuck with using numbers instead of a letter?

=(C3=1)*30+(C3=2)*40+(C3=3)*50+(C3=4)*60+(C3=5)*70+(C3=W)*90

Thanks again

 

[Aladin Akyurek]

On 2002-09-14 23:53, ExcelDummy wrote:
Thanks for the Welcome, you guys are very impressive!!!!

I got the formula to work, but I would like to add a "W" for a different point total. I couldn't get the formula to work, so my next question is, am I stuck with using numbers instead of a letter?

=(C3=1)*30+(C3=2)*40+(C3=3)*50+(C3=4)*60+(C3=5)*70+(C3=W)*90

Thanks again

Try:

=IF(C3="W",90,((C3+0)>0)*20+(C3+0)*10)

This formula expects either a "number" or the letter "W" in C3.

 

[sen_edp]

On 2002-09-14 23:53, ExcelDummy wrote:
Thanks for the Welcome, you guys are very impressive!!!!

I got the formula to work, but I would like to add a "W" for a different point total. I couldn't get the formula to work, so my next question is, am I stuck with using numbers instead of a letter?

=(C3=1)*30+(C3=2)*40+(C3=3)*50+(C3=4)*60+(C3=5)*70+(C3=W)*90

Thanks again

=(C3=1)*30+(C3=2)*40+(C3=3)*50+(C3=4)*60+(C3=5)*70+(C3="W")*90

this does it
This message was edited by sen_edp on 2002-09-15 06:33