1

I'm looking to compute and show individual row totals and a Grand Total. I just need the formulae to put in the boxes so the calculation is automatic but the problem is the calculations are a little complicated...

I'm using data validation to select the day type. This is what I think I need:

  • Assign a price to the day type (either Standard day = £23 or Extended day = £26).
  • Apply a volume discount where appropriate. If Jack is attending all week (5 days) and the day type is the same for all (all Standard or all Extended), the total cost is £100 (or £120)
  • Else the total number of days needs to be added up for Jack. (Number of days for each ‘day type’) and priced up.
  • For his siblings after the first, as above but apply an additional discount of 15%.
  • The grand total then needs to show at the bottom.
pnuts
  • 58,317
  • 11
  • 87
  • 139
user3164057
  • 13
  • 3
  • Thanks pnuts. I believe that "demonstrate a minimal understanding of the problem being solved" is explained in my question. – user3164057 Jan 06 '14 at 22:38

1 Answers1

0

Well, it is not the best of data layouts but this may serve, in L6 and copied down to L13:

=IF(OR(A6="Brother",A6="Sister"),0.85* IF(COUNTIF(B6:F6,"Standard day")=5,100,IF(COUNTIF(B6:F6,"Extended day")=5,120,COUNTIF(B6:F6,"Standard day")*23+COUNTIF(B6:F6,"Extended day")*26)), IF(COUNTIF(B6:F6,"Standard day")=5,100,IF(COUNTIF(B6:F6,"Extended day")=5,120,COUNTIF(B6:F6,"Standard day")*23+COUNTIF(B6:F6,"Extended day")*26)))

and =SUM(L1:L16) in D16.

It would be better practice not to hard code the daily rates/discount, but extracting these from C1:D2 would have increased the length of the formula further.

Note also the result is not £429.95 (you may have changed your example after doing your calculations).

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thank you for your time in solving this problem. It works great! I have modified it slightly so that I can have any name instead of just Brother or Sister. – user3164057 Jan 06 '14 at 22:40
  • If you re-download the WB, I have implemented the changes. Take a look and let me know what you think :) – user3164057 Jan 07 '14 at 14:32
  • Thank you again for your all your help :) - Is there a way for me to credit you other than voting on the answer as Im new, it wont let me vote – user3164057 Jan 07 '14 at 16:46