Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1975011%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1975011%22%20slang%3D%22en-US%22%3EI%20am%20not%20an%20expert%20in%20excel%20formulas%20and%20I%20want%20to%20write%20formula%20that%20reflect%20the%20following%3A%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20have%20a%20three%20brackets%3CBR%20%2F%3E%3CBR%20%2F%3E(0-70)%20%2F%20(70-100)%20%2F%20(any%20number%20over%20100)%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20If%20there%20is%20any%20number%20falls%20within%20these%20brackets%20will%20have%20certain%20percentage%20to%20be%20multiplied%20in%3A%3CBR%20%2F%3E%3CBR%20%2F%3E5%25%20for%20(0-70)%20%2F%2015%25%20for%20(70-100)%20%2F%20100%25%20(any%20number%20over%20100)%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%2C%20if%20the%20number%20is%2075%2C%20so%20the%20percentage%20of%205%25%20will%20be%20applied%20on%20the%2070%20and%2015%25%20will%20be%20applied%20on%20the%20remaining%205%3CBR%20%2F%3E%3CBR%20%2F%3ESame%20concept%20if%20the%20number%20is%20105%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20assistance%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1976153%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1976153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F897158%22%20target%3D%22_blank%22%3E%40Ahmed2500%3C%2FA%3E%26nbsp%3BQuick%20and%20ugly%20since%20I'm%20in%20a%20hurry%2C%20but%20put%20the%20number%20to%20be%20evaluated%20into%20E3%20and%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(0%26gt%3B%24E%243%26gt%3B70%2C%24E%243*0.05%2CIF(77%26gt%3B%24E%243%26gt%3B99%2C(0.05*70)%2B((%24E%243-70)*0.15)%2CIF(%24E%243%26gt%3B100%2C(0.05*70)%2B(0.15*30)%2B(%24E%243-100))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20here%20is%20a%20better%20forum%20for%20questions%20about%20Excel%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fct-p%2FExcel_Cat%22%20target%3D%22_blank%22%3EThe%20Microsoft%20Excel%20Community%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
I am not an expert in excel formulas and I want to write formula that reflect the following:

If I have a three brackets

(0-70) / (70-100) / (any number over 100)

And If there is any number falls within these brackets will have certain percentage to be multiplied in:

5% for (0-70) / 15% for (70-100) / 100% (any number over 100)

Also, if the number is 75, so the percentage of 5% will be applied on the 70 and 15% will be applied on the remaining 5

Same concept if the number is 105

Any assistance?
1 Reply

@Ahmed2500 Quick and ugly since I'm in a hurry, but put the number to be evaluated into E3 and use this formula:

 

=IF(0>$E$3>70,$E$3*0.05,IF(71>$E$3>99,(0.05*70)+(($E$3-70)*0.15),IF($E$3>100,(0.05*70)+(0.15*30)+($E$3-100))))

 

Also, here is a better forum for questions about Excel:

The Microsoft Excel Community