Can Anyone Help Me?

S

Steve

I'm trying to figure out how to do the formula:

if A1 is one through five than B1 = 7
if A1 is six through 10 than B1=8
if A1 is 11 through 13 than B1 is 10
if A1 is 14 through 17 than B1 is 12
if A1 is 18 through 20 than B1is 15
if A1 is 21 than B1 is 16
if A1 is 22 than B1 is 17
if A1 is 23 than B1 is 18
etc

Can anyone help me or tell me what this operation is called?

Thanks
 
S

Steve Huff

I believe you want to use a Select Case statment

Select Case SomeVar
Case 1 to 5
B1 = 7
Case 6 to 10
B1 = 8
....
End Select

Hope this helps...
 
J

James Hahn

Initialize an array
7 7 7 7 7 8 8 8 8 8 10 10 10 12 ...
then reference the array using A1 as the index.
B1=Ar(A1)
 
J

John Vinson

I'm trying to figure out how to do the formula:

if A1 is one through five than B1 = 7
if A1 is six through 10 than B1=8
if A1 is 11 through 13 than B1 is 10
if A1 is 14 through 17 than B1 is 12
if A1 is 18 through 20 than B1is 15
if A1 is 21 than B1 is 16
if A1 is 22 than B1 is 17
if A1 is 23 than B1 is 18
etc
Click to expand...

Another way:

B1: Switch([A1] <= 5, 7, [A1] <= 10, 8, [A1] <= 13, 10, [A1] <= 17,
12, [A1] <= 20, 15, [A1] >= 21, [A1] - 5)


John W. Vinson[MVP]
 
S

Steve

Thank you gentlemen... I appreciate it :)

Steve






John Vinson said:
I'm trying to figure out how to do the formula:

if A1 is one through five than B1 = 7
if A1 is six through 10 than B1=8
if A1 is 11 through 13 than B1 is 10
if A1 is 14 through 17 than B1 is 12
if A1 is 18 through 20 than B1is 15
if A1 is 21 than B1 is 16
if A1 is 22 than B1 is 17
if A1 is 23 than B1 is 18
etc
Click to expand...

Another way:

B1: Switch([A1] <= 5, 7, [A1] <= 10, 8, [A1] <= 13, 10, [A1] <= 17,
12, [A1] <= 20, 15, [A1] >= 21, [A1] - 5)


John W. Vinson[MVP]
Click to expand...
 
S

Steve

I can't seem to find any formulas called Case or SomeVar

I'm having a dickens of a time getting this to work. Please be
remedial with me as I'm not very swift with Excel. Is there another
way to get this to work? I've also tried to copy and paste the formulas
into excell and none of them seem to work. I assumed it would work,
and posted that all was well.....But it's NOT :(

Please Help

Steve
 
L

Larry Daugherty

Hi Steve,

This newsgroup is expressly for Microsoft Access, the Database product.

You'll have better luck posting Excel issues into an Excel newsgroup. In
this case I'd think microsoft.public.excel.programming

What Steve Huff gave was an example of using a Select Case Statement - Look
it up in Help. SH was probably assuming an Access issue so his solution
doesn't quite track.

You'll need to write a function Procedure in VBA (called a "macro" in Excel
parlance) that will process the value passed into it and return another
value based on the algorithm you described. If you've never done it before
it will seem very complicated - it is.

HTH
 
S

Steve Huff

Ahh didn't realize you where talking about Excel. As already mentioned you
need to post to an Excel newsgroup.
 
Top