If condition problem

[thara05]

Hi all,

This is my first post in the forum and hope to learn excel from professionals who are in this forum. Just I start to learn MS Excel and have a confuse in IF function.

This is my question, I am trying to write a function and can't seem to figure it out. Could anybody here help me write this formula correctly?

If total >= 15000                then      Total*12%
If total >= 12000                then      Total*11%
If total >= 9000                  then      Total*10%
If total >= 6000                  then      Total*9%
Else                                              Total*8%

I tried something like this but no any luck.

=IF(total>= 15000.(total/100)*12,IF(total>= 12000.(total/100)*11,IF(total>= 9000.(total/100)*10)))

any idea would be greatly appreciated.

Thank you.

 

[thara05]

oh I forget to divide it by 100

changed the formula and now its work..

=IF(AND(E2>=15000, H2>=2000), E2*10/100, E2*5/100)

can we use LOOKUP for this formula?

 

[Jonmo1]

No need to, you're only comparing 2 values.

Or are you saying you'd want to change the 15000 for E2 to 1200 or something, and the 2000 for H2 to 800 ?
Please provide the details.

 

[thara05]

This is complete date table.

[TABLE="width: 689"]
<colgroup><col span="5"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Account Number [/TD]
[TD]Quater1[/TD]
[TD]Quater2[/TD]
[TD]Quater3[/TD]
[TD]Total [/TD]
[TD]Average [/TD]
[TD]Maximum[/TD]
[TD]Minimum[/TD]
[TD]Interest [/TD]
[TD]Bonus [/TD]
[/TR]
[TR]
[TD]00121[/TD]
[TD]4000[/TD]
[TD]5400[/TD]
[TD]3000[/TD]
[TD]12400[/TD]
[TD="align: right"]4133.33[/TD]
[TD="align: right"]5400[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1364[/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD]00122[/TD]
[TD]6000[/TD]
[TD]7500[/TD]
[TD]6000[/TD]
[TD]19500[/TD]
[TD="align: right"]6500.00[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]2340[/TD]
[TD="align: right"]1950[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[TD]2000[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]6000[/TD]
[TD="align: right"]2000.00[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]540[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]00124[/TD]
[TD]1500[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]4000[/TD]
[TD="align: right"]1333.33[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]00125[/TD]
[TD]5500[/TD]
[TD]4500[/TD]
[TD]5000[/TD]
[TD]15000[/TD]
[TD="align: right"]5000.00[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]00126[/TD]
[TD]4500[/TD]
[TD]4500[/TD]
[TD]5500[/TD]
[TD]14500[/TD]
[TD="align: right"]4833.33[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]1595[/TD]
[TD="align: right"]725[/TD]
[/TR]
[TR]
[TD]00127[/TD]
[TD]1250[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]4250[/TD]
[TD="align: right"]1416.67[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]212.5[/TD]
[/TR]
[TR]
[TD]00128[/TD]
[TD]6000[/TD]
[TD]7500[/TD]
[TD]6000[/TD]
[TD]19500[/TD]
[TD="align: right"]6500.00[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]2340[/TD]
[TD="align: right"]1950[/TD]
[/TR]
[TR]
[TD]00129[/TD]
[TD]7500[/TD]
[TD]7500[/TD]
[TD]7500[/TD]
[TD]22500[/TD]
[TD="align: right"]7500.00[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]2700[/TD]
[TD="align: right"]2250[/TD]
[/TR]
[TR]
[TD]00130[/TD]
[TD]10000[/TD]
[TD]5000[/TD]
[TD]6000[/TD]
[TD]21000[/TD]
[TD="align: right"]7000.00[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]2520[/TD]
[TD="align: right"]2100[/TD]
[/TR]
[TR]
[TD]00131[/TD]
[TD]15000[/TD]
[TD]4500[/TD]
[TD]5000[/TD]
[TD]24500[/TD]
[TD="align: right"]8166.67[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]2940[/TD]
[TD="align: right"]2450[/TD]
[/TR]
[TR]
[TD]00132[/TD]
[TD]7850[/TD]
[TD]5000[/TD]
[TD]6000[/TD]
[TD]18850[/TD]
[TD="align: right"]6283.33[/TD]
[TD="align: right"]7850[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]2262[/TD]
[TD="align: right"]1885
[/TD]
[/TR]
</tbody>[/TABLE]

In this table, I had Quater1, Quater2 and Quater3 values and needed to find out values for Total, Average, Maximum, Minimum, Interest and Bonus.

This is formulas I have used for each

Total : =SUM(B2:D2)
Average : =AVERAGE(B2:D2)
Maximum : =MAX(B2:D2)
Minimum: =MIN(B2:D2)
Interest : =E2*(LOOKUP(E2,{0,6000,9000,12000,15000},{8,9,10,11,12})/100)
Bonus : =IF(AND(E2>=15000, H2>=2000), E2*10/100, E2*5/100)

are these formulas correct?

Thank you.

 

[Jonmo1]

They're correct in the sense that there are no syntax errors in them.
The look like they should do what you want.

The real question is do they provide the results you're expecting?

 

[thara05]

No they don't provide the results.

another question (using about data table), can you tell me how to use the advance filter method to filter the records where savings of the each month is greater than or equal to 4000?

 

[Jonmo1]

OK, can you post showing your 'expected' results? And explain what logic/math leads to those results?

 

[thara05]

you mean?

how can I use advance filter method?

 

[Jonmo1]

I'm still talking about your original problem.

I asked if your formulas provided the correct results, you said no.
I'm following up on that.


1 issue at a time.

 

[thara05]

@jonmo1 Sorry I have misunderstood what you asked, above all formulas give me correct results.

 

[Jonmo1]

Oh OK, Great.

You'd be better of creating a new thread about the Advanced Filter
You'll be more likely to get responses from people who are experienced using it
They'll be less likely to look at it if it's a new question in a thread about an If