Prev: Copy If rows to another worksheet
Next: Nesting IF Statement in excel 2007
From: Bob Zimmerman on 11 May 2010 23:18
The follow work fine in seperate cells.

=IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,(0))
=IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,(0))
=IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,(0))
=IF(AND(R7>0,O7="A"),(R7*I19+50),(0))

What I want to do is combine the 4 above in 1 cell so the results show in a
single cell.

I can't get it to work.
Any help will be appreciated.

Thanks.

Bob Z


From: ozgrid.com on 11 May 2010 23:32
Not sure which order the functions should be, but this combination should
help you;

=IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,
IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,
IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,
IF(AND(R7>0,O7="A"),(R7*I19+50),0))))



--
Regards
Dave Hawley
www.ozgrid.com
"Bob Zimmerman" <BobZimmerman(a)discussions.microsoft.com> wrote in message
news:C2C656BA-C446-4B16-9AFA-DE6A3465593B(a)microsoft.com...
> The follow work fine in seperate cells.
>
> =IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,(0))
> =IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,(0))
> =IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,(0))
> =IF(AND(R7>0,O7="A"),(R7*I19+50),(0))
>
> What I want to do is combine the 4 above in 1 cell so the results show in
> a
> single cell.
>
> I can't get it to work.
> Any help will be appreciated.
>
> Thanks.
>
> Bob Z
>
>

From: Jacob Skaria on 12 May 2010 00:43
Another way..using LOOKUP()

=IF(AND(COUNT(SEARCH({"A","M","Q","S"},O7)),R7>0),
(R7*I19+50)*LOOKUP(O7,{"A","M","Q","S"},{1,0.093,0.27,0.53}),0)

--
Jacob (MVP - Excel)


"Bob Zimmerman" wrote:

> The follow work fine in seperate cells.
>
> =IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,(0))
> =IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,(0))
> =IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,(0))
> =IF(AND(R7>0,O7="A"),(R7*I19+50),(0))
>
> What I want to do is combine the 4 above in 1 cell so the results show in a
> single cell.
>
> I can't get it to work.
> Any help will be appreciated.
>
> Thanks.
>
> Bob Z
>
>
From: Bob Zimmerman on 12 May 2010 08:46
Thank you it works like a charm.

"ozgrid.com" wrote:

> Not sure which order the functions should be, but this combination should
> help you;
>
> =IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,
> IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,
> IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,
> IF(AND(R7>0,O7="A"),(R7*I19+50),0))))
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Bob Zimmerman" <BobZimmerman(a)discussions.microsoft.com> wrote in message
> news:C2C656BA-C446-4B16-9AFA-DE6A3465593B(a)microsoft.com...
> > The follow work fine in seperate cells.
> >
> > =IF(AND(R7>0,O7="M"),(R7*I19+50)*0.093,(0))
> > =IF(AND(R7>0,O7="Q"),(R7*I19+50)*0.27,(0))
> > =IF(AND(R7>0,O7="S"),(R7*I19+50)*0.53,(0))
> > =IF(AND(R7>0,O7="A"),(R7*I19+50),(0))
> >
> > What I want to do is combine the 4 above in 1 cell so the results show in
> > a
> > single cell.
> >
> > I can't get it to work.
> > Any help will be appreciated.
> >
> > Thanks.
> >
> > Bob Z
> >
> >
>
> .
>
 | 
Pages: 1
Prev: Copy If rows to another worksheet
Next: Nesting IF Statement in excel 2007