Forum Discussion
Multiple IF function
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
Mohamed,
Numbers if L7 and next were only for the illustration how it works for different value of collected invoices. In O6 you may use initially suggested formula with helper range
=LOOKUP(N6,$F$24:$F$29,$G$24:$G$29)
or hardcode the range
=LOOKUP(N6,{0,0.25,0.5,0.7,0.8,0.9},{0,0.25,0.5,0.7,0.8,1})or use nested IF
=IF(N6>=0.9,1,IF(N6>=0.8,0.8,IF(N6>=0.7,0.7,IF(N6>=0.5,0.5,IF(N6>=0.25,0.25,0)))))
perhaps some other variants.
Please see attached with all 3 above.