Bit clearer Formula Problem V2

  • Thread starter Thread starter bj
  • Start date Start date
B

bj

A B C D E F G
H

A7402P 1 5 34.9 31.4 28.25 0 0
A7402P 6 11 34.9 31.4 28.25 0 0
A7402P 12 999999 34.9 31.4 28.25 0 0




I have a problem that I cant work out who to do. Ive tried many nested if
statments without any luck.

Basically I have this spreadsheet above full of pricecodes(Col A), Quantity
Breaks (Col C, D) and prices (Col D, E , F, G, H)


In Col I Im trying to enter a formula that will place the price in one
column with their appropriate price breaks.

For the example above the first cell in Col I should be $34.90 then $31.40
then $28.25 (Thats my aim)

Only problem is some pricecodes have only one price break and others can
have upto 5.

The one constant is that all pricecodes have a minimum of 1 and a maximum of
999999


Can anyone out there help me how I would do this?

Thanks in advance.
 
bj,

Written for row 1:

=IF(D1<>0,TEXT(D1,"$0.00"),"")&IF(E1<>0,TEXT(E1,",
$0.00"),"")&IF(F1<>0,TEXT(F1,", $0.00"),"")&IF(G1<>0,TEXT(G1,",
$0.00"),"")&IF(H1<>0,TEXT(H1,", $0.00"),"")

HTH,
Bernie
MS Excel MVP
 
Hey Bernie, thanks for your quick reply. ALmost there I think.

It helped me somewhat however your formula puts all the price breaks in one
cell seperated by a ,<space>

On the sample below using your formula in I1
I get $34.90, $31.40, $28.25 this repeats for I2 and I3

What I need is
I1=$34.90
I2=$31.40
I3=$28.25


Any ideas?
 
Try this.

In I2, add

=IF(INDEX($D$2:$H$2,1,ROW(A1))<>0,TEXT(INDEX($D$2:$H$2,1,ROW(A1)),"$0.00"),"
")

and copy down
 
Back
Top