Applying a discount to a 5th Day using IF formula

[ChrisHallas]

Hi Guys...

Need help with an IF formula which I'm sure is possible but it's giving me head aches.

I run a child day care facility and currently use excel to calculate fees for parents. I'm trying to write an easier way to do it.

Basically it's N amount per day, but if you attend full time (Mon-Fri) it's a discounted rate and not just N times 5.

I've already created a table whereby you put a "Y" under Monday it takes how many Mondays we're open throughout the year, and divides it by 12 to give you a monthly cost of fees. This works great for anybody who doesn't attend Full Time, as they can see what the monthly cost would be (for example) to do Wednesdays instead of Mondays (we're open more Wednesdays as we shut on Bank Holiday Mondays) or also (another example) put a "Y" under several days to see what the difference in cost would be for 2 days p/w or 3 etc etc.

But I'd somehow like it to realise that if there is a "Y" under all 5 days, it puts a different figure all together in.

Seems like it should be simple to do but I just can't do it. I'm pretty sure this problem hasn't translated very well into text but if anyone thinks they know what I'm talking about any help at all would be appreciated!

Probably easier if you check out the attachment.

Cheers!

[zbor]

Hi Chris,

can you pleasre rename your title according to the rules:

Forum Rules And Guidance
1. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[Richard Buttrey]

Hi Chris,

I'm attaching your workbook with an additional sheet containing a solution. You can obviously hide rows 1:2 when you've added the variables.

Put the discounted cost, (I've assumed an absolute value) in G2. You could of course change this to a % and modify the E8 formula accordingly.

HTH

[zbor]

Pritty much same solution Help!(1).xls as Richard Buttrey-

Differencies: mine formula look is there Y and your look is there anything (whatever hw likes).

Also, I would add that it's better to define somewhere values (like here in table from I to O),
rather than writting values in formulas (like ChrisHallas did) because it can be easier changed when you need.

For example, changing rate for wed is easier in one table than look in all formulas in workbook.