Hi Experts,
I have an excel file where I want to calculate commission according to the formula, If I type 1 the amount automatically show 1% of the amount in Commission columns, If I type 2 or 3 or 4., I'm attached screenshot and excel file, you will better understand what I want to say,
Thank is Advance
Hi
changed your table a bit and used this formula:
=C2*LOOKUP(B2,I4:I18,J4:J18)
Last edited by Limor_OP; 10-31-2020 at 05:41 PM.
Thanks for the help, I need a formula in a cell, I just show criteria for describing my issue
No Problem,
Turn this into constant numbers:
=C2*LOOKUP(B2,{0;3;6;7;8;9;10;11;12;13;14;15},{0.05;0.1;0.12;0.13;0.14;0.15;0.16;0.17;0.18;0.19;0.2;0.21})
Please try
=C2*TEXT(MIN(B2+6,21),"[>11]0;[>8]1\0;5")%
Hi Bo_Ry
Can you please explain your formula?
especially this part: [>11]0;[>8]1\0;5
Thanks.
=--TEXT(MIN(B2+6,21),"[>11]0;[>8]1\0;5")
similar to
=If(MIN(B2+6,21)>11,MIN(B2+6,21),if(MIN(B2+6,21)>8,10,5)
"[>11]0;[>8]1\0;5"
is custom number formats based on conditions
https://www.ablebits.com/office-addi...number-format/
Very nice trick
Thanks for the clear explanation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks