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.
 

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
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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
5,029
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,209
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
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top