IF formula assistance

[paulfurlong]

hi, I hope someone might be able to help me formulate a simple bonus payment spreadsheet!

I am trying to fathom "less than greater than" syntax in a formula without much success...!

In simple terms I am trying to find a formula that says:

In n27 i need a formula "if n26 (total revenue) figure is between 62000 and 105,000 then work out 10% of that figure, if not return 0"

In n28 I need a formula "if n26 is 105,001 and greater then return a value of 30% of that figure, if not return 0"

Any help is much appreaciated!
Thanks in advance

 

[barry houdini]

Try like this

In N27

=IF(AND(N26>=62000,N26<=105000),10%*N26,0)

In N28

=IF(N26>105000,30%*N26,0)

 

[JamesW]

Hi,

N27: =IF(AND(N26>=62000,N26<=105000),N26*0.1,0)
N28: =IF(N26>105000,N26*0.3,0)

HTH,

James

 

[paulfurlong]

Thanks, but my apologies!!! I wrote it wrong... it should be

In n27 i need a formula "if n26 (total revenue) figure is between 62000 and 105,000 then work out n26-62000*10%, if not return 0"

In n28 I need a formula "if n26 is 105,000 and greater then return a value of 30% of anything greater than 105,001, if not return 0"


Thanks again!

 

[barry houdini]

So if N26 is 120,000 you'd expect N27 to return zero and N28 to return 4,500 (30% of 15,000)?

 

[paulfurlong]

Thanks Barry,
Nearly.... if n26 is 120,000 then the person should get as bonus 10% of anything between 62,000 and 105,000 = 4,300 (n27)
if the earn over 105,000 eg 120,00 then they also get 4,500 as you suggested (figure in n28)

But conversley if the figure is 100,00 in n26, then they only get the 10% of the amount between 62,000 and 100,000 = 3800. Therefore n28 will be 0.

hope that makes sense!

thanks again, really appreciate it.