Nov 01 2019 09:53 PM
I work in a gym and want to calculate points from sales.
Each cell has a dropdown for the correct outcome
C1 is Membership Type 3 MONTHS OR 12+ MONTHS
D1 is Joining Fee, YES OR NO
F1 is Personal Training Pack, 2 Pack, 6 Pack, 12 Pack
E1 is just a cell where its YES or NO to whether they took PT and is no used in the formula.
3 month is worth 0.5 and 12+months is worth 0.75
Joining Fee when YES is 0.25 and 0 when NO
Personal Training Pack is 0.25 for 2 pack, 0.5 for the 6 pack and 0.5 for the 12 pack
I am trying to create a formula that works for this. I have an sum(IF statement that partially works.
For example
C1 is 3 month (0.5) D1 is YES (0.25) F1 is 6 Pack (0.5) which sums up to 1.25 in G1 cell where the formula is located.
also another example
C1 is 12 month (0.75) D1 YES (0.25) F1 is 2 Pack which is (0.25) = 1.25
C1 is 12 month (0.75) D1 No (0) and F1 is left blank so 0= 0.75
Any ideas people?
Nov 01 2019 10:33 PM
Build three reference tables and use VLOOKUP() in the main table to lookup the points. Add up the three lookup results. Done.