# at a loss for a formula

#### ExcelDummy

##### New Member
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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

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,"")

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

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

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

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.

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

Replies
3
Views
383
Replies
6
Views
253
Replies
6
Views
229
Replies
1
Views
100
Replies
7
Views
172

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back