Calculate according to the maximum range of values ​​from the Excel IF Formula - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Wednesday, January 1, 2020

Calculate according to the maximum range of values ​​from the Excel IF Formula

Calculate according to the maximum range of values ​​from the Excel IF Formula.

In this article, we are going to use IF Formula in a different way. If we use the IF condition to perform calculations within the maximum number limit.

In the example, losses up to 50000 have to be repaid. If Actual Loss Amount or 50000, which is less amount, that amount is considered for Compensation.

Arguments.

In the IF Formula, we need Logical Test, Value if True and Value if False these 3 Arguments.
IF formula in excel | Example for Value Base Calculation | Tax Rebate Example | Set Maximum Value Limit |

1. Logical Test - Condition Here is the condition - IF Actual Loss Amount is 50000 or more than 50000 Then

2. Value IF True - Compensation 50000.



3. Value IF False - Else Compensation as much as loss.



Steps - 


1. Apply the condition by typing in cell E3, if the value of D3 is 50000, or more than 50000, and put a comma.

= IF(D3=>50000,

2. When the value IF True is highlighted, type 50000 value, and type comma. Meaning if according to the above condition the value of Cell D3 is 50000 or more than 50000 then show only the value 50000.

3. If Value False is highlighted then type D3 and close the bracket & Hit Enter. This means if the above logical test (condition) shows that if the value of cell D3 is less than 50000 then show only the value of D3.
=IF(D3>=50000,50000,D3)

IF formula in excel | Example for Value Base Calculation | Tax Rebate Example | Set Maximum Value Limit |

In the above example, we have imposed only one condition.

Now in the following example, we will apply two conditions.
In the tax calculation, in the rebate calculation we have to set the rebate limit along with the tax limit. As if the taxable amount is less than 50000, then rebate amount  2000 or tax Amount which is less amount will be rebate.

Condition 1 

IF D3>500000 then 0
else Condition 2
Meaning if the value of taxable amount cell D3 in condition 1 is more than 500000, then rebate zero and taxable amount is less then condition 2.
=IF(D3>500000,0,Condition 2 )

Condition 2 

IF(E3>=2000 then,2000 
Else E3

Now in Condition 2, if the value of Tax Amount cell E3 is 2000 or more than 2000, then Rebate Value is 2000, Else Tax Value Cell E3.
 IF(E3>=2000,2000,E3)



Such a complete formula is made.

=IF(D3>500000,0,IF(E3>=2000,2000,E3))



In this way, if the first condition is full filled, then the second condition will be applied.


IF formula in excel | Example for Value Base Calculation | Tax Rebate Example | Set Maximum Value Limit |

Download Formula IF formula in excel Workbook

In this way, you can do different calculation limits according to different conditions. In the IF formula, we can apply more than one IF Formulas. You can also set the value in the formula according to the result text. If you want to learn more such examples, then comment and we will bring more examples for you. Thank you.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment