#### Pls Help on this

```Dear Sir's

How to Put Formula for this Query

If A1 <150000 Then A2 = A1+20%
If A1>150000 and <500000 then A2=A1+15 %
If A1>500000 Then A2 = 0

Pls Help me

Regards,
Mahesh

``` 0 8/10/2005 7:47:37 AM excel  39879 articles. 2 followers. 9 Replies 623 Views Similar Articles

[PageSpeed] 6

```Good morning Mahesh

This formula will do exactly as you requested:

=IF(A1>500000,0,IF(A1>150000,A1*1.15,IF(A1<150000,A1*1.1,"-")))

BUT, you haven't specified what is to happen if A1=150,000.  Th
formula above will return "-" in this instance, but you may want t
replace one of the instances above with "<=" or ">=" to specify wha
Excel is to do if this happens.

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1893

``` 0 8/10/2005 8:27:54 AM
```=A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))

--
HTH

Bob Phillips

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
> Dear Sir's
>
> How to Put Formula for this Query
>
>     If A1 <150000 Then A2 = A1+20%
>     If A1>150000 and <500000 then A2=A1+15 %
>     If A1>500000 Then A2 = 0
>
> Pls Help me
>
> Regards,
> Mahesh
>
>
>

``` 0 8/10/2005 8:31:11 AM
```Thanks Mr.Bob

But i want formula in A2 for all the condition ( i.e IF A1<150000 Then A2 =
A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
A>500000 then 0

Pls Help me once again

Regards,
Mahesh

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
> =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
>
> --
> HTH
>
> Bob Phillips
>
> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
>> Dear Sir's
>>
>> How to Put Formula for this Query
>>
>>     If A1 <150000 Then A2 = A1+20%
>>     If A1>150000 and <500000 then A2=A1+15 %
>>     If A1>500000 Then A2 = 0
>>
>> Pls Help me
>>
>> Regards,
>> Mahesh
>>
>>
>>
>
>

``` 0 8/10/2005 9:01:25 AM
```Mahesh,

That is exactly what I think my formula does.

In what way do you see it as wrong?

--
HTH

Bob Phillips

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
> Thanks Mr.Bob
>
> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then A2
=
> A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
> A>500000 then 0
>
> Pls Help me once again
>
> Regards,
> Mahesh
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
> > =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> > news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
> >> Dear Sir's
> >>
> >> How to Put Formula for this Query
> >>
> >>     If A1 <150000 Then A2 = A1+20%
> >>     If A1>150000 and <500000 then A2=A1+15 %
> >>     If A1>500000 Then A2 = 0
> >>
> >> Pls Help me
> >>
> >> Regards,
> >> Mahesh
> >>
> >>
> >>
> >
> >
>
>

``` 0 8/10/2005 9:23:03 AM
```Dear Friends,

Here is one more problem i have . Pls help me to solve this

Condition                                     Amount Return
IF B1 <150000  30000.00
IF B1 >150000 & <300000 25000.00
IF B1 >300000 & <500000 20000.00
IF B1 >500000  0.00

I written formula as
=IF(B1>=500001,0,IF(OR(B1>=300001,B1<=500000),20000,IF(OR(B1>=150001,B1<=300000),25000,IF(B1<=150000,30000,"0"))))

But it is not returns value after my second if condtion I.e.IF I Put any
amount in B1 below 500000 it returns only "20000" and if Write more than or
= 500001 it returs 0 , That meance it is not taking Condition if for 25000
and 30000 amount return

Pls Help Me as to put correct formula

Regards,
Mahesh
"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
> Thanks Mr.Bob
>
> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then A2
> = A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
> A>500000 then 0
>
> Pls Help me once again
>
> Regards,
> Mahesh
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
>> =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
>> news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
>>> Dear Sir's
>>>
>>> How to Put Formula for this Query
>>>
>>>     If A1 <150000 Then A2 = A1+20%
>>>     If A1>150000 and <500000 then A2=A1+15 %
>>>     If A1>500000 Then A2 = 0
>>>
>>> Pls Help me
>>>
>>> Regards,
>>> Mahesh
>>>
>>>
>>>
>>
>>
>
>

``` 0 8/10/2005 9:47:13 AM
```Dear Friends,

I think you will find difficulty in the format of my Query, as i was copied
from excel and pasted here !! So i am clearly wring the detail .i.e.
condition and return

My Condition is : IF B1 < 150,000 Then B2=30,000 , IF B1 >150,001 & <
300,000 Then B2=25,000 ,IF B1>300,001 & <500,000 Then B2=20,000, IF
B>500,000 Then B2=0

Regards,
Mahesh

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:OUtlACZnFHA.1468@TK2MSFTNGP12.phx.gbl...
> Dear Friends,
>
> Here is one more problem i have . Pls help me to solve this
>
>
> Condition                                     Amount Return
>      IF B1 <150000  30000.00
>      IF B1 >150000 & <300000 25000.00
>      IF B1 >300000 & <500000 20000.00
>      IF B1 >500000  0.00
>
>
>
> I written formula as
> =IF(B1>=500001,0,IF(OR(B1>=300001,B1<=500000),20000,IF(OR(B1>=150001,B1<=300000),25000,IF(B1<=150000,30000,"0"))))
>
>
> But it is not returns value after my second if condtion I.e.IF I Put any
> amount in B1 below 500000 it returns only "20000" and if Write more than
> or = 500001 it returs 0 , That meance it is not taking Condition if for
> 25000 and 30000 amount return
>
> Pls Help Me as to put correct formula
>
>
> Regards,
> Mahesh
> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
>> Thanks Mr.Bob
>>
>> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then A2
>> = A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
>> A>500000 then 0
>>
>> Pls Help me once again
>>
>> Regards,
>> Mahesh
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
>>> =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
>>>
>>> --
>>> HTH
>>>
>>> Bob Phillips
>>>
>>> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
>>> news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
>>>> Dear Sir's
>>>>
>>>> How to Put Formula for this Query
>>>>
>>>>     If A1 <150000 Then A2 = A1+20%
>>>>     If A1>150000 and <500000 then A2=A1+15 %
>>>>     If A1>500000 Then A2 = 0
>>>>
>>>> Pls Help me
>>>>
>>>> Regards,
>>>> Mahesh
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

``` 0 8/10/2005 10:02:23 AM
```=IF(B1<150000,30000,IF(B1<300000,25000,IF(B1<500000,20000,0)))

--
HTH

Bob Phillips

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:OJ2XdKZnFHA.3120@TK2MSFTNGP09.phx.gbl...
> Dear Friends,
>
> I think you will find difficulty in the format of my Query, as i was
copied
> from excel and pasted here !! So i am clearly wring the detail .i.e.
> condition and return
>
> My Condition is : IF B1 < 150,000 Then B2=30,000 , IF B1 >150,001 & <
> 300,000 Then B2=25,000 ,IF B1>300,001 & <500,000 Then B2=20,000, IF
> B>500,000 Then B2=0
>
> Regards,
> Mahesh
>
>
>
> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> news:OUtlACZnFHA.1468@TK2MSFTNGP12.phx.gbl...
> > Dear Friends,
> >
> > Here is one more problem i have . Pls help me to solve this
> >
> >
> > Condition                                     Amount Return
> >      IF B1 <150000  30000.00
> >      IF B1 >150000 & <300000 25000.00
> >      IF B1 >300000 & <500000 20000.00
> >      IF B1 >500000  0.00
> >
> >
> >
> > I written formula as
> >
=IF(B1>=500001,0,IF(OR(B1>=300001,B1<=500000),20000,IF(OR(B1>=150001,B1<=300
000),25000,IF(B1<=150000,30000,"0"))))
> >
> >
> > But it is not returns value after my second if condtion I.e.IF I Put any
> > amount in B1 below 500000 it returns only "20000" and if Write more than
> > or = 500001 it returs 0 , That meance it is not taking Condition if for
> > 25000 and 30000 amount return
> >
> > Pls Help Me as to put correct formula
> >
> >
> > Regards,
> > Mahesh
> > "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> > news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
> >> Thanks Mr.Bob
> >>
> >> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then
A2
> >> = A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
> >> A>500000 then 0
> >>
> >> Pls Help me once again
> >>
> >> Regards,
> >> Mahesh
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
> >>> =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
> >>>
> >>> --
> >>> HTH
> >>>
> >>> Bob Phillips
> >>>
> >>> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> >>> news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
> >>>> Dear Sir's
> >>>>
> >>>> How to Put Formula for this Query
> >>>>
> >>>>     If A1 <150000 Then A2 = A1+20%
> >>>>     If A1>150000 and <500000 then A2=A1+15 %
> >>>>     If A1>500000 Then A2 = 0
> >>>>
> >>>> Pls Help me
> >>>>
> >>>> Regards,
> >>>> Mahesh
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>

``` 0 8/10/2005 10:36:16 AM
```In B2, enter:
=IF(B1<150000,30000,IF(B1<300000,25000,IF(B1<500000,20000,0)))

Mangesh

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:OJ2XdKZnFHA.3120@TK2MSFTNGP09.phx.gbl...
> Dear Friends,
>
> I think you will find difficulty in the format of my Query, as i was
copied
> from excel and pasted here !! So i am clearly wring the detail .i.e.
> condition and return
>
> My Condition is : IF B1 < 150,000 Then B2=30,000 , IF B1 >150,001 & <
> 300,000 Then B2=25,000 ,IF B1>300,001 & <500,000 Then B2=20,000, IF
> B>500,000 Then B2=0
>
> Regards,
> Mahesh
>
>
>
> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> news:OUtlACZnFHA.1468@TK2MSFTNGP12.phx.gbl...
> > Dear Friends,
> >
> > Here is one more problem i have . Pls help me to solve this
> >
> >
> > Condition                                     Amount Return
> >      IF B1 <150000  30000.00
> >      IF B1 >150000 & <300000 25000.00
> >      IF B1 >300000 & <500000 20000.00
> >      IF B1 >500000  0.00
> >
> >
> >
> > I written formula as
> >
=IF(B1>=500001,0,IF(OR(B1>=300001,B1<=500000),20000,IF(OR(B1>=150001,B1<=300
000),25000,IF(B1<=150000,30000,"0"))))
> >
> >
> > But it is not returns value after my second if condtion I.e.IF I Put any
> > amount in B1 below 500000 it returns only "20000" and if Write more than
> > or = 500001 it returs 0 , That meance it is not taking Condition if for
> > 25000 and 30000 amount return
> >
> > Pls Help Me as to put correct formula
> >
> >
> > Regards,
> > Mahesh
> > "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> > news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
> >> Thanks Mr.Bob
> >>
> >> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then
A2
> >> = A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
> >> A>500000 then 0
> >>
> >> Pls Help me once again
> >>
> >> Regards,
> >> Mahesh
> >>
> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
> >>> =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
> >>>
> >>> --
> >>> HTH
> >>>
> >>> Bob Phillips
> >>>
> >>> "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> >>> news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
> >>>> Dear Sir's
> >>>>
> >>>> How to Put Formula for this Query
> >>>>
> >>>>     If A1 <150000 Then A2 = A1+20%
> >>>>     If A1>150000 and <500000 then A2=A1+15 %
> >>>>     If A1>500000 Then A2 = 0
> >>>>
> >>>> Pls Help me
> >>>>
> >>>> Regards,
> >>>> Mahesh
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>

``` 0 8/10/2005 10:46:49 AM
```Mahesh,

the formula given by Bob is correct.
Atleast try it once before commenting on it.

Mangesh

"Mahesh" <mahesh@kohinoorprinters.com> wrote in message
news:uem6ZoYnFHA.1468@TK2MSFTNGP12.phx.gbl...
> Thanks Mr.Bob
>
> But i want formula in A2 for all the condition ( i.e IF A1<150000 Then A2
=
> A1*20% ,Else if  A1 > 150000 and A1 < 500000 then A2 = A1*15% Else If
> A>500000 then 0
>
> Pls Help me once again
>
> Regards,
> Mahesh
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u57x8XYnFHA.3312@tk2msftngp13.phx.gbl...
> > =A1*(IF(A1<150000,120%,IF(A1<500000,115%,0)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Mahesh" <mahesh@kohinoorprinters.com> wrote in message
> > news:Ol28J\$XnFHA.2472@tk2msftngp13.phx.gbl...
> >> Dear Sir's
> >>
> >> How to Put Formula for this Query
> >>
> >>     If A1 <150000 Then A2 = A1+20%
> >>     If A1>150000 and <500000 then A2=A1+15 %
> >>     If A1>500000 Then A2 = 0
> >>
> >> Pls Help me
> >>
> >> Regards,
> >> Mahesh
> >>
> >>
> >>
> >
> >
>
>

``` 0 8/10/2005 10:48:25 AM Similar Artilces: