I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

Look at this example; modify to suit your needs:
http://www.contextures.com/xlFunctions02.html

Regards,
Ryan---

--
RyGuy


"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

These tips were both VERY helpfull and thank you both. However, I'm still
running into a couple of problems with it. I need to be able to input all
data into the one cell and get the outcome in another. In other words, I need
the VLOOKUP to be able to see 1S, 1L as two different entities and give me
the sum of each. That poses yet another problem. When it comes to the
quantities of each of the two(or however many) entities. Maybe this wasn't
meant to be. LOL But thank you again for your assistance. :-)

"Air_Cooled_Nut" wrote:

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

I'm thinking how I would tackle this and that would be with a custom function
(a macro). If you need to keep it to standard Excel functions then you may
want to post in the microsoft.public.excel.functions group. It sounds like a
complex formula because the number of entities can vary.
--

Toby Erkson
http://excel.icbm.org/


"markythesk8erboi" wrote:

These tips were both VERY helpfull and thank you both. However, I'm still
running into a couple of problems with it. I need to be able to input all
data into the one cell and get the outcome in another. In other words, I need
the VLOOKUP to be able to see 1S, 1L as two different entities and give me
the sum of each. That poses yet another problem. When it comes to the
quantities of each of the two(or however many) entities. Maybe this wasn't
meant to be. LOL But thank you again for your assistance. :-)

"Air_Cooled_Nut" wrote:

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

........Um...... I can do that.... I think.... So how do I do that?


"Air_Cooled_Nut" wrote:

I'm thinking how I would tackle this and that would be with a custom function
(a macro). If you need to keep it to standard Excel functions then you may
want to post in the microsoft.public.excel.functions group. It sounds like a
complex formula because the number of entities can vary.
--

Toby Erkson
http://excel.icbm.org/


"markythesk8erboi" wrote:

These tips were both VERY helpfull and thank you both. However, I'm still
running into a couple of problems with it. I need to be able to input all
data into the one cell and get the outcome in another. In other words, I need
the VLOOKUP to be able to see 1S, 1L as two different entities and give me
the sum of each. That poses yet another problem. When it comes to the
quantities of each of the two(or however many) entities. Maybe this wasn't
meant to be. LOL But thank you again for your assistance. :-)

"Air_Cooled_Nut" wrote:

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman

You'll need to know VBA for Excel. Referencing your original post of
"...Please keep in mind you will have to be very clear to me what I must do
for I am dumb. LOL " along with your question I suspect that this is beyond
your skill set :-( Using VBA isn't nearly as easy as a formula or lookup
table. You'll need to get professional help for this situation or else
figure out how to make due with what you can currently do.

You may want to have a work sheet that you do your data calculations and
other various work on, then have a main sheet (one that your "customer" would
use) that references the proper cells for the answers. You could Hide the
work sheet so the user couldn't see it, thus keeping your workbook cleaner
looking.
--

Toby Erkson
http://excel.icbm.org/


"markythesk8erboi" wrote:

.......Um...... I can do that.... I think.... So how do I do that?


"Air_Cooled_Nut" wrote:

I'm thinking how I would tackle this and that would be with a custom function
(a macro). If you need to keep it to standard Excel functions then you may
want to post in the microsoft.public.excel.functions group. It sounds like a
complex formula because the number of entities can vary.
--

Toby Erkson
http://excel.icbm.org/


"markythesk8erboi" wrote:

These tips were both VERY helpfull and thank you both. However, I'm still
running into a couple of problems with it. I need to be able to input all
data into the one cell and get the outcome in another. In other words, I need
the VLOOKUP to be able to see 1S, 1L as two different entities and give me
the sum of each. That poses yet another problem. When it comes to the
quantities of each of the two(or however many) entities. Maybe this wasn't
meant to be. LOL But thank you again for your assistance. :-)

"Air_Cooled_Nut" wrote:

Your lookup cell will contain the formula:
=VLOOKUP(A2,MyTable,2,FALSE)
where A2 is the cell you're checking (or change it to whatever cell you want
to) and MyTable is the name of the range of cells that contain the lookup
table. Here's what your lookup table should look like:
1S 0.24
1L 0.41
2S 0.43
3PL 0.51
SPL 0.78
TPL 2.37
NTPL 1.36
TTPL 3.5
You put your lookup values in one column and their corrisponding result in
the column next to it.

This is the simple way. Doing the math would be:
=2 * VLOOKUP(A2,MyTable,2,FALSE)
or
=A3*VLOOKUP(A2,MyTable,2,FALSE)
where cell A3 contains the number you want to use as the multiplier.

Toby Erkson
http://excel.icbm.org/

"markythesk8erboi" wrote:

I currently have this formula in place :

H9=IF(G9="1S",0.24,IF(G9="1L",0.41,IF(G9="2S",0.43 ,IF(G9="3PL",0.51,IF(G9="SPL",0.78,IF(G9="TPL",2.3 7,IF(G9="NTPL",1.36,IF(G9="TTPL",3.5,0))))))))

1) Is there an easier way to have this outcome?
2) How do I set up the formula so that I can input multiple phrases? i.e.
"1L,2S,SPL" = 0.41+0.43+0.78.
3) Can I also set up quantities of each? i.e. "2,1L,1,2S,1,SPL" = (2 x 0.41)
+ (1 x 0.43) + (1 x 0.78)

Please keep in mind you will have to be very clear to me what I must do for
I am dumb. LOL
Thank you for your help in this matter!

Mark Feldman