Help with creating a formula

[SCOPE Adult Day Care]

Howdy all - I am a very novice Excel user that is having problems coming up with the correct formulas to use in a spreadsheet I am creating for work.

What I need is this:

Column A = 3 Variables (1, 2 or 3)

Column B = If Column A= 1 or 2, then Column B=1. If Colum A = 3, then Column B = Manual entry of #

Column C = If Column A = 1, then 6.48. If Column A=2, then 18.24. If Column A=3, then 1.36

Column D = Column B multiplied by Column C.

This may seem like a stupid or easy question, but I just haven't been able to figure out the correct formulas necessary to do the calculations that I need.

Any and all help would be greatly appreciated.

Dennis

[Asala42]

Hey,

Column A = 3 Variables (1, 2 or 3)

Column B = If Column A= 1 or 2, then Column B=1. If Colum A = 3, then Column B = Manual entry of #

Column C = If Column A = 1, then 6.48. If Column A=2, then 18.24. If Column A=3, then 1.36

Column D = Column B multiplied by Column C.

Column B: =IF(OR(A2=1,A2=2),1,"Manual entry of #")

Column C: =IF(A2=1,6.28,IF(A2=2,18.24,1.36))
*note that if A2= something than 1,2,or 3 it will still output 1.36.

Column D: =B2*A2

Hope that helps,
Adam

[Yogi Anand]

Hi Dennis:
Welcome! put the following formulas in cells

in B1 ... =IF(OR(A1={1,2}),1,IF(A1=3,"Manual Entry"))
in C1 ... =IF(A1=1,6.48,IF(A1=2,18.24,IF(A1=3,1.36,"whatever")))
in D1 ... =B1*C1

Please post if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

[bergy]

Asala42 answer works great, but I have added a little extra so that the cells are blank until the A column cell is filled in. Doesn't work any better, but it might look nicer to the user.

B1: =IF(OR(A1=1,A1=2),1,"")
C1: =IF(A1=1,6.48,IF(A1=2,18.24,IF(A1=3,1.36,"")))
D1: =IF(B1="","",B1*C1)

[Aladin Akyurek]

Worded a bit differently from what Adam already suggested:

In B1 enter:

=IF(OR(A1={1,2}),1,"Manual entry of #")

In C1 enter:

=(A1=1)*6.48+(A1=2)*18.24+(A1=3)*1.36

In D1 enter:

=IF(ISNUMBER(B1),B1*C1,0)


Aladin

[Asala42]

Hey Aladin

=(A1=1)*6.48+(A1=2)*18.24+(A1=3)*1.36

I like that. I'll have to use that next time something like that comes up.

Adam

[SCOPE Adult Day Care]

THANK YOU ALL VERY MUCH FOR THE HELP!!!!!!!!

Worked like a charm!!!

[SCOPE Adult Day Care]

Hello again - While working with the new formulas that you all helped me with last week, I realized that I need something else added to the problem I posed to you.

What I neglected to realize was that in column A, I need 4 variables, not 3

What I need is this:

Column A = 4 Variables (0, 1, 2 or 3)

Column B = If Column A=0, then Column B=0. If Column A= 1 or 2, then Column B=1. If Colum A = 3, then Column B = Manual entry of #

Column C = If Column A = 0, then 0.00. If Column A = 1, then 6.48. If Column A=2, then 18.24. If Column A=3, then 1.36

Column D = Column B multiplied by Column C.


So that is the new ? I pose to you - again, i am sure its not that difficult to come up with the correct formulas, but as a new Excel user, I have been unable to figure the correct formula out.

Thank you in advance for your help!!!

Dennis

[Dave Patton]

consider B1

=IF(A1=0,0,IF(A1=1,1,IF(A1=2,1,IF(A1=3,"Manual Entry of #","????"))))

What do you want if number is not 0-3.
I put ????.

C1
=(A1=1)*6.48+(A1=2)*18.24+(A1=3)*1.36

[Aladin Akyurek]

Hello again - While working with the new formulas that you all helped me with last week, I realized that I need something else added to the problem I posed to you.

What I neglected to realize was that in column A, I need 4 variables, not 3

What I need is this:

Column A = 4 Variables (0, 1, 2 or 3)

Column B = If Column A=0, then Column B=0. If Column A= 1 or 2, then Column B=1. If Colum A = 3, then Column B = Manual entry of #

Column C = If Column A = 0, then 0.00. If Column A = 1, then 6.48. If Column A=2, then 18.24. If Column A=3, then 1.36

Column D = Column B multiplied by Column C.


So that is the new ? I pose to you - again, i am sure its not that difficult to come up with the correct formulas, but as a new Excel user, I have been unable to figure the correct formula out.

Dennis

In B1 enter:

=VLOOKUP(A1,{0,0;1,1;3,"Manual entry of #"},2)

In C1 enter:

=(A1=1)*6.48+(A1=2)*18.24+(A1=3)*1.36

In D1 enter;

=IF(ISNUMBER(B1),B1*C1,0)

The only change, it seems to me, is to B1.

PS. I did not include a check for an entry like 1.5 in A1.

Aladin