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
mahesh1 (8)
8/10/2005 7:47:37 AM
excel 39879 articles. 2 followers. Follow

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
View this thread: http://www.excelforum.com/showthread.php?threadid=39448

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
bob.phillips1 (6510)
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
mahesh1 (8)
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
bob.phillips1 (6510)
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
mahesh1 (8)
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
mahesh1 (8)
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
bob.phillips1 (6510)
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
Reply:

Similar Artilces: