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

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

Or better yet.... I can expand my table to include multiples so that it could
be
=VLOOKUP(G2,TABLE,(F2),0) where F2 will hold the value of the quantity used.
This still doesn't solve my entire problem but it helps

"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

I like to keep things simple if at all possible. With that in mind...

Put this in cell H2:
=LEFT(G2,LEFT(FIND(",",G2)-1))*VLOOKUP(MID(G2,FIND(",",G2)+1,LEN(G2)),Table,2 ,FALSE)

I used the Table from my example above. I noticed your VLOOKUP was looking
at column 3, whereas mine uses column 2 (where the actual numbers are).

To get a neat way of seeing how this formula operates, click on the Tools
main menu and select "Formula Auditing" and then "Evaluate Formula". Click
on the "Evaluate" button to watch what happens. The text above the button
will explain what the underline and italics mean. I love that tool!
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

BTW, good job on showing what you are looking for in your pseudo-formula.
That helped me with ideas on what to build. That's a good programming
practice :-)

A couple of things about the formula I wrote. No spaces allowed in the
lookup table and none in the cell contents that is being looked at -- in our
examples it's cell G2.
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

Well, thank you for the kudos!!! YAY!!! Now, how would you like to get REALLY
complicated? I have another formula that I THINK is working right but I want
to make sure. I'm getting the outcome I want but I'm not sure the syntax is
correct which might be a problem in the future.


Here Goes:

Column "I" is set up so that if Column "B" = "SPECIFIC NAME" then 9.95 else 0
I2=IF(B2="Online",9.95,0)

Column "J" has a numeric value I enter.
Column "K" has this formula K2=J2-(J2*0.1) or simply put 90% of J
Column "L" has a numeric value I enter.
Column "M" has a name reference for my table. Ex. "3PL"
Column "N" looks up "M" and returns the value of L*"3PL"
=VLOOKUP(M2,Sheet4!$A$1:$L$9,L2+2(to get to the right column of my
table),FALSE)*L2

Column "O" has a reference to another table. Ex. "SPL-*$*-TPL"
Column "P" looks up "O" and returns the proper value.
Column "Q" references back to table 1 and returns the proper value.
Column "R"...... This is the fun one.....

=VLOOKUP(Q2,Sheet4!$A$1:$L$10,3,FALSE)+(P2-N2)+(J2-K2)+IF(I20,I2-J2,0)

So, VLOOKUP for Q2 in table1, column3,false + the difference between P2
and N2
+ the
difference between J2 and K2
+ the
difference between I2 and J2

if I2 is greater then 0.

Sound right?

"Air_Cooled_Nut" wrote:

BTW, good job on showing what you are looking for in your pseudo-formula.
That helped me with ideas on what to build. That's a good programming
practice :-)

A couple of things about the formula I wrote. No spaces allowed in the
lookup table and none in the cell contents that is being looked at -- in our
examples it's cell G2.
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

Everything looks okay to me. Hard to say if it will break in the future but
as long as certain rules are followed that should help keep errors down.
Breaking down the steps like you've done helps make following the resultant
formula easier to understand. Your column R formula is fine and really not
that bad, plus you did the right thing by using paranthesis
(P2-N2)+(J2-K2)...so many people don't use them and that can cause problems
later on.

For the table_array used in the VLOOKUP, Sheet4!$A$1:$L$9, if you give it a
distinct name then you can use that name anywhere in your workbook. Naming
the range really makes it easier to understand/read the formula in the
future. Just a recommendation.
--

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


"markythesk8erboi" wrote:

Well, thank you for the kudos!!! YAY!!! Now, how would you like to get REALLY
complicated? I have another formula that I THINK is working right but I want
to make sure. I'm getting the outcome I want but I'm not sure the syntax is
correct which might be a problem in the future.


Here Goes:

Column "I" is set up so that if Column "B" = "SPECIFIC NAME" then 9.95 else 0
I2=IF(B2="Online",9.95,0)

Column "J" has a numeric value I enter.
Column "K" has this formula K2=J2-(J2*0.1) or simply put 90% of J
Column "L" has a numeric value I enter.
Column "M" has a name reference for my table. Ex. "3PL"
Column "N" looks up "M" and returns the value of L*"3PL"
=VLOOKUP(M2,Sheet4!$A$1:$L$9,L2+2(to get to the right column of my
table),FALSE)*L2

Column "O" has a reference to another table. Ex. "SPL-*$*-TPL"
Column "P" looks up "O" and returns the proper value.
Column "Q" references back to table 1 and returns the proper value.
Column "R"...... This is the fun one.....

=VLOOKUP(Q2,Sheet4!$A$1:$L$10,3,FALSE)+(P2-N2)+(J2-K2)+IF(I20,I2-J2,0)

So, VLOOKUP for Q2 in table1, column3,false + the difference between P2
and N2
+ the
difference between J2 and K2
+ the
difference between I2 and J2

if I2 is greater then 0.

Sound right?

"Air_Cooled_Nut" wrote:

BTW, good job on showing what you are looking for in your pseudo-formula.
That helped me with ideas on what to build. That's a good programming
practice :-)

A couple of things about the formula I wrote. No spaces allowed in the
lookup table and none in the cell contents that is being looked at -- in our
examples it's cell G2.
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

Well I do have A LITTLE programming experience so I am well aware of the
importance of paranthesis in syntax. I had a friend suggest I write a program
using Visual Basic that would do all this work for me and then just dump the
outcome into an Excel file.... Maybe I'll do that!! But for now this will
suffice and thank you again for all your help on this matter. :-) Now, if I
could just get it make me breakfast every morning I'll be SET!!!!! LOL

"Air_Cooled_Nut" wrote:

Everything looks okay to me. Hard to say if it will break in the future but
as long as certain rules are followed that should help keep errors down.
Breaking down the steps like you've done helps make following the resultant
formula easier to understand. Your column R formula is fine and really not
that bad, plus you did the right thing by using paranthesis
(P2-N2)+(J2-K2)...so many people don't use them and that can cause problems
later on.

For the table_array used in the VLOOKUP, Sheet4!$A$1:$L$9, if you give it a
distinct name then you can use that name anywhere in your workbook. Naming
the range really makes it easier to understand/read the formula in the
future. Just a recommendation.
--

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


"markythesk8erboi" wrote:

Well, thank you for the kudos!!! YAY!!! Now, how would you like to get REALLY
complicated? I have another formula that I THINK is working right but I want
to make sure. I'm getting the outcome I want but I'm not sure the syntax is
correct which might be a problem in the future.


Here Goes:

Column "I" is set up so that if Column "B" = "SPECIFIC NAME" then 9.95 else 0
I2=IF(B2="Online",9.95,0)

Column "J" has a numeric value I enter.
Column "K" has this formula K2=J2-(J2*0.1) or simply put 90% of J
Column "L" has a numeric value I enter.
Column "M" has a name reference for my table. Ex. "3PL"
Column "N" looks up "M" and returns the value of L*"3PL"
=VLOOKUP(M2,Sheet4!$A$1:$L$9,L2+2(to get to the right column of my
table),FALSE)*L2

Column "O" has a reference to another table. Ex. "SPL-*$*-TPL"
Column "P" looks up "O" and returns the proper value.
Column "Q" references back to table 1 and returns the proper value.
Column "R"...... This is the fun one.....

=VLOOKUP(Q2,Sheet4!$A$1:$L$10,3,FALSE)+(P2-N2)+(J2-K2)+IF(I20,I2-J2,0)

So, VLOOKUP for Q2 in table1, column3,false + the difference between P2
and N2
+ the
difference between J2 and K2
+ the
difference between I2 and J2

if I2 is greater then 0.

Sound right?

"Air_Cooled_Nut" wrote:

BTW, good job on showing what you are looking for in your pseudo-formula.
That helped me with ideas on what to build. That's a good programming
practice :-)

A couple of things about the formula I wrote. No spaces allowed in the
lookup table and none in the cell contents that is being looked at -- in our
examples it's cell G2.
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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

Hahaha, well, I can't help with the breakfast (even though I'm the one who
does it at my place) but there's one book I would recommend if you want to
learn some more about VBA programming and it's "Excel 2002 Power Programming
with VBA", ISBN: 0-7645-4799-2 (there's a 2003 version but apparently there
wasn't much extra added). This book alone taught me so much; it's an
EXCELLENT resource and very well written! (For anyone who reads every single
post, yes, I copied this from another recent post of mine...the book is damn
good!).

Yes, with VBA, you could create your own formulas and get really fancy.
Just remember, a cell formula can only return a value to the cell that called
it (at most), it can never change a cell somewhere else. That's a real
****er for many but that's how Excel works and you can't easily get around
that (if at all?) :-(

Good luck!
--

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


"markythesk8erboi" wrote:

Well I do have A LITTLE programming experience so I am well aware of the
importance of paranthesis in syntax. I had a friend suggest I write a program
using Visual Basic that would do all this work for me and then just dump the
outcome into an Excel file.... Maybe I'll do that!! But for now this will
suffice and thank you again for all your help on this matter. :-) Now, if I
could just get it make me breakfast every morning I'll be SET!!!!! LOL

"Air_Cooled_Nut" wrote:

Everything looks okay to me. Hard to say if it will break in the future but
as long as certain rules are followed that should help keep errors down.
Breaking down the steps like you've done helps make following the resultant
formula easier to understand. Your column R formula is fine and really not
that bad, plus you did the right thing by using paranthesis
(P2-N2)+(J2-K2)...so many people don't use them and that can cause problems
later on.

For the table_array used in the VLOOKUP, Sheet4!$A$1:$L$9, if you give it a
distinct name then you can use that name anywhere in your workbook. Naming
the range really makes it easier to understand/read the formula in the
future. Just a recommendation.
--

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


"markythesk8erboi" wrote:

Well, thank you for the kudos!!! YAY!!! Now, how would you like to get REALLY
complicated? I have another formula that I THINK is working right but I want
to make sure. I'm getting the outcome I want but I'm not sure the syntax is
correct which might be a problem in the future.


Here Goes:

Column "I" is set up so that if Column "B" = "SPECIFIC NAME" then 9.95 else 0
I2=IF(B2="Online",9.95,0)

Column "J" has a numeric value I enter.
Column "K" has this formula K2=J2-(J2*0.1) or simply put 90% of J
Column "L" has a numeric value I enter.
Column "M" has a name reference for my table. Ex. "3PL"
Column "N" looks up "M" and returns the value of L*"3PL"
=VLOOKUP(M2,Sheet4!$A$1:$L$9,L2+2(to get to the right column of my
table),FALSE)*L2

Column "O" has a reference to another table. Ex. "SPL-*$*-TPL"
Column "P" looks up "O" and returns the proper value.
Column "Q" references back to table 1 and returns the proper value.
Column "R"...... This is the fun one.....

=VLOOKUP(Q2,Sheet4!$A$1:$L$10,3,FALSE)+(P2-N2)+(J2-K2)+IF(I20,I2-J2,0)

So, VLOOKUP for Q2 in table1, column3,false + the difference between P2
and N2
+ the
difference between J2 and K2
+ the
difference between I2 and J2

if I2 is greater then 0.

Sound right?

"Air_Cooled_Nut" wrote:

BTW, good job on showing what you are looking for in your pseudo-formula.
That helped me with ideas on what to build. That's a good programming
practice :-)

A couple of things about the formula I wrote. No spaces allowed in the
lookup table and none in the cell contents that is being looked at -- in our
examples it's cell G2.
--

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


"markythesk8erboi" wrote:

Ok, I get a gold star!!! So, I had a brainstorm and came up with this
'ALMOST' solution:

G2 H2
2,1S =(G2 before the comma)*VLOOKUP((G2 after the
comma),Table,3,0)


Can you tell me how I might do this? It would work given that I only use one
of the phrases but any quantity of those phrases. And it doesn't HAVE to be a
comma I can use any seperator even just a space. Do you know a formula for
that?

"Air_Cooled_Nut" wrote:

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