Bit clearer Formula Problem V2

[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.

[Bernie Deitrick]

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

"bj" <[email protected]> wrote in message
news:[email protected]...
>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]

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?



"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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
>
> "bj" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
> >
>
>

[Bob Phillips]

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

--
HTH

Bob Phillips

"bj" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
> > 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
> >
> > "bj" <[email protected]> wrote in message
> > news:[email protected]...
> > >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]

No luck with that Bob.


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "bj" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > "bj" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Bob Phillips]

What does that do in your case?

--
HTH

Bob Phillips

"bj" <[email protected]> wrote in message
news:[email protected]...
> No luck with that Bob.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "bj" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> > >
> > >
> > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > > "bj" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>