Excel Ch. 9 Grader Project. Help please!


In the range C15:G15, insert a function to calculate the total daily revenue. In the range H11:H15, insert a function to calculate the weekly totals for the seating categories and grand total. 




Try to approach the task logically. In order to calculate the total daily revenue you need revenue data. The data must include amounts and dates. You need the amounts to combine them into a total and you need a date to separate the revenues from different days. So, still talking pure logic only, you would look for a formula which sums up only those amounts which can be associated with a particular date. In Excel, the formula to use is SUMIF(). Google for "Excel SUMIF function" and you will get more help than you can easily stomach.

Encouraged? Try the same logical approach with the second task. A function to calculate the weekly totals for the seating categories and grand total isn't possible. Any number you calculate can only be either a grand total or weekly total. On the face of it the line of logic seems clear enough, however. Sum up the daily totals for a week to make a weekly total, then sum up the weekly totals to create a grand total. However, there is that bit about seating categories which muddies the waters. We need a different approach.

Let's presume that you can't use the daily totals you already have because they disregard seating categories. In that case you would have to go back to the original data (revenue amounts by day), extract the weekly total from them (instead of daily) but filtered by seating category. For this purpose Excel has the SUMIFS() function. Where SUMIF allows you just one selection criterium (above it was the date) SUMIFS allows many. For example, you might say something like "include the amount in the total if the date is >= [Start of the week] AND the date is <= [End of the week]. Note that "the date" is repeated twice, once for each comparison.

That would give you a weekly total but still no seating category. Luckily there is no limit to the number of conditions you can concatenate in the SUMIFS function. You can simply add one to specify a particular seating category. Google for SUMIFS to get more specific guidance.

What I want to impress upon you here is the need to move from the logic to the formula. Trying the other way - from the formula to the logic - doesn't work. You need to look at your data. See how they are arranged. Then find the logical answer to your task like I have demonstrated above. And then, only in the final step, translate your logic into a formula (or function). Use your understanding of the logic to find the question. Then formulate your question precisely to look for an answer to it.

When you need help in finding the question you must show the data because that is where the logic is hidden. After you found the question google for an answer. That's what I do - many times most days.


Answer the Question

You must create an account to use the forum. Create an Account or Login