at a loss for a formula

ExcelDummy

New Member
Joined
Sep 13, 2002
Messages
11
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Hello, and welcome

I hope this example suits your need
Book1
ABCD
1130
2240
3350
4460
5570
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,464
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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,"")
 

ExcelDummy

New Member
Joined
Sep 13, 2002
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Mar 13, 2002
Messages
555
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,767
Members
412,291
Latest member
marypolitan
Top