# 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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### sen_edp

##### Well-known Member
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
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,"")

##### MrExcel MVP
=(A1>0)*20+A1*10

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

#### ExcelDummy

##### New Member

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

##### MrExcel MVP
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
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
1
Views
108
Replies
0
Views
873
Replies
3
Views
348
Replies
27
Views
749
Replies
2
Views
188

### Forum statistics

1,148,272
Messages
5,745,791
Members
423,973
Latest member
man_this_is_hard ### 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