Calculations that handle different units

[LurfysMa]

I need to create a spreadsheet to calculate the annual time share
percentages that are allocated to the two parents in a child custody
case.

The time throughout the year is typically allocated in three block:
vacation (about 75 days), holidays (about 38 days), and everything
else (about 252 days, when school is in session). The total days must
sum to 365.

Each of the blocks may be specified either as a percentage or as a
number of days. For example, it might be something like this. There
are 6 input cells: the first 2 cvalues in the first 3 rows. The rest
are calculated.

Father Mother Father% Mother% Block Days
30% 70% 30% 70% School time 252
20 days 55 days 27% 73% Summer time 75
50% 50% 50% 50% Holiday time 38
115 days 250 days 32% 68% Totals 365

The school time schedule can also be entered as how many days each
parent gets in 1, 2, or 3 weeks.

Father Mother Father% Mother% Block Days
3.5 days 10.5 days 25% 75% School time 252
20 days 55 days 27% 73% Summer time 75
50% 50% 50% 50% Holiday time 38
102 days 263 days 28% 72% Totals 365

I would like to set up the 6 input cell so the user could enter the
parameter as a percentage ("35%") or a number of days ("22 days") and
have the spreadsheet figure out what to do.

Do I need to write a macro?

Is there a better wauy to approach this?

 

[Guest]

Hi LurfysMa:

What an interesting name.

I would suggest having a data area where you enter the data and say if it is
percentage or days.

You can then have a report area where you present the answers.

The cell (in say A1) for percentage or days could be P or D and then in your
some you use an if statement:

=IF(A1="P",365*A2,A2)

for days etc.

and for percentages:

=IF(A1="D",A2/365%,A2)

You need to consider about leap years.

Hope this gets you started.

And lets see how you get on.

 

[LurfysMa]

I need to create a spreadsheet to calculate the annual time share
percentages that are allocated to the two parents in a child custody
case.

The time throughout the year is typically allocated in three block:
vacation (about 75 days), holidays (about 38 days), and everything
else (about 252 days, when school is in session). The total days must
sum to 365.

Each of the blocks may be specified either as a percentage or as a
number of days. For example, it might be something like this. There
are 6 input cells: the first 2 values in the first 3 rows. The rest
are calculated.

Father Mother Father% Mother% Block Days
30% 70% 30% 70% School time 252
20 days 55 days 27% 73% Summer time 75
50% 50% 50% 50% Holiday time 38
115 days 250 days 32% 68% Totals 365

The school time schedule can also be entered as how many days each
parent gets in 1, 2, or 3 weeks.

Father Mother Father% Mother% Block Days
3.5 days 10.5 days 25% 75% School time 252
20 days 55 days 27% 73% Summer time 75
50% 50% 50% 50% Holiday time 38
102 days 263 days 28% 72% Totals 365

I would like to set up the 6 input cell so the user could enter the
parameter as a percentage ("35%") or a number of days ("22 days") and
have the spreadsheet figure out what to do.

Do I need to write a macro?

Is there a better wauy to approach this?

In case anyone is interested, I came up with a solution that works
very nicely for me, but may not be a great general solution.

I defined a pair of cells entitled "P1 Part" and "Out Of". In the P1
Part cell I put the portion one parent gets in some representative
block of time. In the Out Of cell, I put the time in the block. I have
Excel calculate the percentage and use that to calculate the actual
number of days.

In the example below, taken from the shreadsheet, Parent 1 gets 3.5
days every 2 weeks (14 days) during the school year, 4 weeks out of 11
weeks in the summer, and 50% out of 100% of the holidays. The units
don't matter because they get divided out:

Block Days P1 Part Out Of P1% P2% P1 Days P2 Days Total
School 252 3.50 14.00 25% 75% 63 189 252
Summer 76 4.00 11.00 36% 64% 28 48 76
H'days 38 50.00 100.00 50% 50% 19 19 38
Totals 366 30% 70% 110 256 366