If then statements

[Kathleen]

Hi,

I've rarely used the if then statements and am not sure is what I need to do
is possible. A1 is a list box with 12 options. Its made up of 3 groups
consisting of 4 different rates per group for a total list box of 12 options.
B1 is a charge based on A1 which is pulled from a separate worksheet using
vlookup. C1 is a yes or no list box. D1 needs to see yes in C1 and then
look at A1 and its corresponding rate sheet to enter an applicable credit
rate based on one of the groups.

I'll try to give an example.

A1 is a list box with Single a, Single b, Single C, double a, double b,
double c or triple a, triple b, or triple c. Each have a different rate

B1 looks at A1, uses vlookup on separate tab to get corresponding rate

C1 is a yes or no list box.

If C1 is yes, then D1 needs to look at A1. If it says Single A, Double A or
Triple A, then enter that rate. If it says Double b, Double c, Triple b or
c, then the D1 needs enter either Single A, Double A or Triple A.

Single a = 5
Single b = 10
Single c = 15

Double a = 3
Double b = 6
Double c = 9

Triple a = 4
Triple b = 8
Triple c = 12

If Single a, Double a or Triple a, then 5, 3 or 4
If Single b, c, Double b, c or Triple b, c, then enter the single, double or
triple a rate.

If 5, then 5
If 10, then 5
If 15, then 5

If 3, then 3
If 6, then 3
If 9, then 3

If 4, then 4
If 8, then 4
If 12, then 4

Does this make sense? Is there a way to do this?

Kathleen

 

[David Heaton]

Hi,

I've rarely used the if then statements and am not sure is what I need todo
is possible. A1 is a list box with 12 options.  Its made up of 3 groups
consisting of 4 different rates per group for a total list box of 12 options.
 B1 is a charge based on A1 which is pulled from a separate worksheet using
vlookup.  C1 is a yes or no list box.  D1 needs to see yes in C1 and then
look at A1 and its corresponding rate sheet to enter an applicable credit
rate based on one of the groups.

I'll try to give an example.

A1 is a list box with Single a, Single b, Single C, double a, double b,
double c or triple a, triple b, or triple c.  Each have a different rate

B1 looks at A1, uses vlookup on separate tab to get corresponding rate

C1 is a yes or no list box.  

If C1 is yes, then D1 needs to look at A1.  If it says Single A, DoubleA or
Triple A, then enter that rate.  If it says Double b, Double c, Triple b or
c, then the D1 needs enter either Single A, Double A or Triple A.  

Single a = 5
Single b = 10
Single c = 15

Double a = 3
Double b = 6
Double c = 9

Triple a = 4
Triple b = 8
Triple c = 12

If Single a, Double a or Triple a, then 5, 3 or 4
If Single b, c, Double b, c or Triple b, c, then enter the single, doubleor
triple a rate.

If 5, then 5
If 10, then 5
If 15, then 5

If 3, then 3
If 6, then 3
If 9, then 3

If 4, then 4
If 8, then 4
If 12, then 4

Does this make sense?  Is there a way to do this?

Kathleen

Kathleen,


Try this

=IF(LEFT(A1,1)="T",B1*3,IF(LEFT(A1,1)="D",B1*2,B1))

Regards

David

 

[Liliana]

Hi,

I've rarely used the if then statements and am not sure is what I need
to do is possible. A1 is a list box with 12 options. Its made up of 3
groups consisting of 4 different rates per group for a total list box
of 12 options.
B1 is a charge based on A1 which is pulled from a separate worksheet
using
vlookup. C1 is a yes or no list box. D1 needs to see yes in C1 and
then look at A1 and its corresponding rate sheet to enter an
applicable credit rate based on one of the groups.

I'll try to give an example.

A1 is a list box with Single a, Single b, Single C, double a, double
b, double c or triple a, triple b, or triple c. Each have a different
rate

B1 looks at A1, uses vlookup on separate tab to get corresponding rate

C1 is a yes or no list box.

If C1 is yes, then D1 needs to look at A1. If it says Single A,
Double A or Triple A, then enter that rate. If it says Double b,
Double c, Triple b or c, then the D1 needs enter either Single A,
Double A or Triple A.

Single a = 5
Single b = 10
Single c = 15

Double a = 3
Double b = 6
Double c = 9

Triple a = 4
Triple b = 8
Triple c = 12

If Single a, Double a or Triple a, then 5, 3 or 4
If Single b, c, Double b, c or Triple b, c, then enter the single,
double or triple a rate.

If 5, then 5
If 10, then 5
If 15, then 5

If 3, then 3
If 6, then 3
If 9, then 3

If 4, then 4
If 8, then 4
If 12, then 4

Does this make sense? Is there a way to do this?

Kathleen

Is this what you need?

B2 formula

=IF(C2="Yes",VLOOKUP(A2,myRateList,2),"")

D2 formula

=IF(C2="Yes",VLOOKUP(LEFT(A2,7) & "A",myRateList,2),"")


Assumption: your lookup list uses Single A, Double A, Tiple A, etc as
above.

Change myRateList to your lookup list range.

 

[Liliana]

Is this what you need?

B2 formula

=IF(C2="Yes",VLOOKUP(A2,myRateList,2),"")

D2 formula

=IF(C2="Yes",VLOOKUP(LEFT(A2,7) & "A",myRateList,2),"")


Assumption: your lookup list uses Single A, Double A, Tiple A, etc as
above.

Change myRateList to your lookup list range.

Also, you need to ensure you lookup list is sorted:

Double A
Double B
....etc
Single A
Single B
.....etc
Triple A
....


--

 

[Kathleen]

Hi David, Liliana,

Thank you for the formulas. They are working for me in another section but
I can't get either to work for this particular problem.

Here's part of the formula that works so far.

=VLOOKUP(A1,'rate sheet'!A2:B14,2,FALSE)

This is in B1 and looks at A1 to see which one of the options is listed,
then goes to the rate sheet and picks the corresponding rate. What I'm
looking for is to add, I think, three additional steps.

C1 is a yes or no field D1 is another rate type column. I'd like D1 to look
at C1 and if it says yes, then look at A1 to see which option is listed, then
to go the rate sheet to get the corresponding rate, and finally (this is a
new part), take the chosen rate and multiply it by 65%.

Is that possible?

Kathleen

 

[David Heaton]

Hi David, Liliana,

Thank you for the formulas.  They are working for me in another sectionbut
I can't get either to work for this particular problem.

Here's part of the formula that works so far.  

=VLOOKUP(A1,'rate sheet'!A2:B14,2,FALSE)

This is in B1 and looks at A1 to see which one of the options is listed,
then goes to the rate sheet and picks the corresponding rate.  What I'm
looking for is to add, I think, three additional steps.

C1 is a yes or no field D1 is another rate type column.  I'd like D1 tolook
at C1 and if it says yes, then look at A1 to see which option is listed, then
to go the rate sheet to get the corresponding rate, and finally (this is a
new part), take the chosen rate and multiply it by 65%.

Is that possible?

Kathleen








- Show quoted text -

Kathleen,

I'm struggling to understand your problem correctly. it seems to me
that B1 and D1 are the same except for the fact that you want to check
the value in C1 and then apply the 65%

if thats the case try this in D1

=IF(C1="YES",VLOOKUP(A1,'rate sheet'!A2:B14,2,FALSE) *.65,"")

If this still isnt right you can send me your sheet so I have a better
understanding

Regards

David

 

[Liliana]

Try =VLOOKUP(A3,RateSheet!A1:B6,FALSE) instead of =VLOOKUP
(A1,'rate
sheet'!A2:B14,2,FALSE)

(note the absence of single quote marks).


Also, my D2 formula would be more robust if you used the
following
instead (not sure what David said to use - his response is not
in my
reader):

=IF(B3="Yes",VLOOKUP(LEFT(A3,LEN(A3)-1) & "A",myratelist,2),"")


To multiply by 65% just add "*.65" to the formula (without the
quotes).

HTH

--
Lil


=?Utf-8?B?S2F0aGxlZW4=?= <[email protected]>
wrote in