Qty x cost from 1 colume

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rbell - 21 Jan 2006 14:29 GMT

I have a workbook that has a list of part numbers & Qtys. It looks up
the cost from 3 different Mfg product line lists and returns the cost
in 3 different columes. Now I need to multiply the Qty. x the cost but
I only want it done for 1 colume. Some of the pn's are in 1,2, or 3 of
the columes. I use IF(ISNA) to put 0's in when it's not on one of the
lists. Some Pn's are used in 1 or more lines & some are not. So far
I've not come up with a good formula. Thanks in advance for any ideas.

PN qty cost1 cost2 cost3 total cost
111 2 1.00 0 0
222 1 0 1.00 0
333 3 1.00 1.00 0
444 2 1.00 1.00 1.00

Signature

rbell

Reply to this Message

Dave Peterson - 21 Jan 2006 15:10 GMT

I'm not sure how you get the total cost using just one field if the costs could
vary (not counting 0)....

But maybe:

=b2*max(c2:e2)

Where B held the Qty and C:E held the individual costs.

I'm not sure if it's worth it, but you may want to make one Cost column and then
use an indicator for each line:

pn qty Cost Line1 line2 line3 totalcost
111 2 1.0 Y N N 2.00

Well, it kind of makes sense to me--but that's from someone who has no idea what
you're doing <vbg>.

> I have a workbook that has a list of part numbers & Qtys. It looks up
> the cost from 3 different Mfg product line lists and returns the cost
[quoted text clipped - 13 lines]
> rbell
> Posted via http://ms-os.com Forum to Usenet gateway

Signature

Dave Peterson

Reply to this Message

rbell - 22 Jan 2006 12:22 GMT

Hi Dave Your suggestion seems to work. I was trying to make it too
complicated.
Here is a little better explanation of what I'm doing. The inventory
contains part numbers in A & Qty's in B. There are about 1100 pn's. I
then use this formula
=IF(ISNA(INDEX(Sheet1!G:G,MATCH(A2,Sheet1!A:A,0))),0,INDEX(Sheet1!G:G,MATCH(A2,Sheet1!A:A,0)))
to search a master list for each product line (about 38000 pn's) for the
cost. A=PN & G=cost. I do this once for each product line which gives me
3 columns of cost's. The IF(ISNA) puts 0 in if it's not on a particular
PL list. Some parts are unique to a Pl and some are used in more than
1, like hardware etc. So the cost can be in more than 1 column. Also I
can only get the master lists in Excell from the Mfg. by PL. The cost
will be the same if used on more than 1 PL. I then used your formula to
get a total value of each PN. Then I SUM that column for the whole
inventory. This seems to be correct although I've only check a few.
Thanks again Dick Bell

Signature

rbell

Reply to this Message