Hello,
I just have two (2) queries about nested if, Vlookup, Count or Sum if formulas
1 60% of cost/quote (Column F)
2 80% of cost/quote (Column F)
3 Based on ADA schedule (each) or 70% of the cost (whichever is less) Column G & F
4 Based on ADA schedule (each) or 75% of the cost (whichever is less) Column G & F
5 Based on ADA schedule Column G
6 Based on ADA schedule ($98.00 each) or 75% of the cost (whichever is less) Column G & F
- Column E is the number from 1-6 (as above)
- The results output into column H
The current formula:
=IF(E3=1,F3*0.6,IF(E3=2,F3*0.8,IF(AND(E3=3,G3*A3<F3*A3*0.7),G3*A3,F3*A3*0.7))) & I can't work out how to have the result as '0' if there is no number being validated as above
I am unsure whether to use more nested if statements (which I get a limit error) or try something else. I also can't get the formula to validate anything past number 3 as above so having trouble extending it to include all the numbers 1-6
The other issue I face is this:
I have text entered in one (1) cell only, this could be any one of say six different texts
What's the formula to say if the cell containing text (L2), contains nothing or is blank, then do nothing, otherwise take a total, subtract the results of a a range of numbers in this case H3:H11. i.e K18-(H3:H11) if there is text in L2
This is to say the family limit is to be reduced by the benefits payable if the product has a family limit applicable (obviously this I would have also only typed a balance in there if it's applicable) So the formula could also be based on K18 being more than zero 0.00
Thank you
Bookmarks