Forum Discussion
Multiple IF function
Hi Mohamed,
Better to create helper table with ranges for your commissions. It could be as a range or even better as Excel table (see on the top here)
When the formula to calculate commission will be
=LOOKUP(B10,$B$2:$B$7,$C$2:$C$7)
in first case or
=LOOKUP(E10,PerCent[Collected],PerCent[Commission])
for the table (at the bottom of the screenshot), and in attached file
Hello Sergei
thanks for your response ...but still not clear for me
could you please clarify it more
i have attached my excel file so you can test it on the way i need it to be worked
appreciate your support
I moved the ranges here
The formula with it is
=LOOKUP(N6,$F$24:$F$29,$G$24:$G$29)
And in attached file.
In your initial logic is not clear what to do if the % of collected invoice is between boundaries, e.g. 49.5% (you have ranges 25-49% and 50-69%). In my ranges if less than 50% is one percent, if more or equal to 50% is another one. But that could be changed by selecting proper boundaries.
Thank You Again for your swift reply
however i just need the collector to add the number of invoices in one cell ( yellow cell ) L6 and based on that % Achieved is calculated N6...then i need If formula to read the range to apply the correct % in O6...for example is he collect 170 invoice means 85% from target then % to apply in O6 to be 80% without having all the table from cell L7:L26...just 1 cell to the number of invoices collected and rest automatically calculated without user interference...can you support with IF formula to read range >= and <= ?
appreciate
thanks
