(upbeat music) - [Oz] This is a good one
and you're really going to have to think about this,
so let's take it slow.
These five friends, Eli, Margo,
Sheila, Sven and Taara agreed
to go on vacation together
and this is how they split the expenses.
They're going to split the car rental equally
between the five friends.
And I gave you a headstart with that.
It's $99.78 per person.
The general expenses,
they will be divided equally.
The beach house rental is the juicy part.
Not everybody could be there the entire 14 days.
So they decided it this way.
Whomever was in the beach house that day
would divide the daily rate equally.
On day one, Sheila and Sven will split
whatever the day rate is between the two of them.
We go down to day seven,
all five split the day rate equally.
I hope that's clear.
So go ahead, get everybody's total,
and make sure that it's within a reasonable range
of the total of the expenses,
car rental and the beach house rental, all right?
They might be a little off
because of rounding but they shouldn't be way off.
So go ahead.
Take a deep breath.
Think about it.
Pause the video, work that out, come back
and I will do it.
All right, let's dive into it.
Let's see, first of all,
I'm going to get a sum
in anticipation of needing to check the figures
in the end.
Equals sum, open parentheses here.
Okay, and then I'm going to make this bold.
Now, let's calculate what each person owes
for general expenses.
Cursor here.
Equals,
the general expenses, F4 to lock down that cell reference.
Divided by COUNTA,
open parentheses, the column of friends,
close parentheses, enter.
And I'm going to highlight and look down
in the lower right.
That sum is $1,081.10.
Beautiful.
Now I'm going to add another column.
House.
Now to calculate what people owe toward the rental.
Let's go over here.
Daily.
Equals.
The beach house number
divided by COUNTA.
Open parentheses.
The 14 days.
Close parentheses, Enter.
$210.06 per day.
I'm going to go over here
and do per person.
Equals the daily,
F4 divided by COUNTA,
open parentheses
here.
Close parentheses, Enter.
Yup, $105.03 is half of $210.06.
We're doing okay.
We go up here.
Double click.
Now right click,
get data from table/range.
The table does have headers.
Okay.
I don't need this column.
Right click it, remove.
I want the rate over on the other side.
Right click it, move,
to beginning.
With it still highlighted,
right click, unpivot other columns
and then group by.
Leave it as attribute.
Do a sum of the per person column.
OK.
Now, close and load.
Let's bring this up.
Get rid of the filter button.
Bring this over here.
Equals XLOOKUP.
Look up Eli,
comma, look up where?
Look for Eli here.
Comma, what to return?
The count.
Enter.
Wow.
Now, the total.
Equals the sum,
open parentheses, enter.
Let's convert this to money.
Now, does this add up?
Want to click inside the table.
Table Design.
Total row.
Oh my gosh, look at that.
The $4,520.83
matches the $4,520.83.
Isn't that beautiful?
(upbeat music)