Auto fill unknown values from available data: day, month and year.

Quintus · Sep 8th 2017

First post and an infant in regards to excel so thanks in advance.

Im trying to do a bit of an expenditure budget so please bear with me.

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

Weekly

[/td]

[td]

Monthly

[/td]

[td]

Yearly

[/td]

[td][/td]

[/tr]

[tr]

[td]

Bills

[/td]

[td][/td]

[/tr]

[tr]

[td]

Morguage

[/td]

[td][/td]

[/tr]

[/TABLE]

If i only have the weekly values of my bills but i want the monthly and yearly columns auto fill
or
If i only have the mothly values of my mortguage but i want the weekly and the yearly columns to auto fill

I have been using some basic code but its not working how i would like it to.
I would also like for the box to retun to formula if the value isnt manually imput.

Hope that all makes sense and TIA

TheGlovner · Sep 12th 2017

Re: Auto fill unknown values from available data: day, month and year.

Are you using the year to calculate both Month & Weeks?

i.e.

YearlyTotalFromMonth = MonthlyTotal * 12
YearlyTotalFromWeeky = WeeklyTotal * 52
MonthlyTotal = YearlyTotal / 12
WeeklyTotal = YearlyTotal /52

So if someone input a monthly total it would calculate the yearly total and then use this result to calculate the weekly total?

TheGlovner · Sep 12th 2017

Re: Auto fill unknown values from available data: day, month and year.

[TABLE="width: 500"]

[tr]

[td]

Row\Column

[/td]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[td]

F

[/td]

[td]

G

[/td]

[td]

H

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td][/td]

[td]

[TABLE="width: 108"]

[tr]

[td]

WeeklyInput

[/td]

[/tr]

[/TABLE]

[/td]

[td]

MonthlyInput

[/td]

[td]

YearlyInput

[/td]

[td][/td]

[td]

WeeklyCalc

[/td]

[td]

MonthlyCalc

[/td]

[td]

YearlyCalc

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Category

[/td]

[td][/td]

[td]

=IFERROR(IF(LEN($B2)>0, $B2, $I2/52), "")

[/td]

[td]

=IFERROR(IF(LEN($C2)>0, $C2, $I2/12), "")

[/td]

[td]

=IF(LEN($D2)>0, $D2, IF(LEN($C2)>0, $C2*12, IF(LEN($B2)>0, $B2*52, "")))

[/td]

[/tr]

[/TABLE]

TheGlovner · Sep 12th 2017

Re: Auto fill unknown values from available data: day, month and year.

So I've taken a bash at solving your problem.

I don't think the answer is to try and use code to solve it, seems like over engineering to me.

To get round the idea of overtyping formulas and having to use code to address the formula being lost I would strip out the Input and keep this separate from the calculation.

So in the above example you have an input area (Columns B, C & D) and a calculation area (Columns F, G & H).

If you enter a yearly value, this is used to populate Column H, calculate Column F (Input / 52) and calculate Column G (Input / 12)
If you enter a weekly value, this is used to populate Column F, calculate Column H (Input * 52), then Column H is used to calculate Column G (Yearly / 12)
If you enter a monthly value, this is used to populate Column G, calculate Column H (Input * 12), then Column H is used to calculate Column F (Yearly / 52)

This way you don't have to worry about overtyping or replacing formula.

Usually good practice to separate your Inputs from your working.

Quintus · Sep 13th 2017

Re: Auto fill unknown values from available data: day, month and year.

Hi TheGlovner

Thanks for your help
I agreee with you that we will have to keep the imputs separate from the calcs.
I have decided to re arrange the setup to a week by week setup so as to be more accurate with a wage input and a bills output.

Still learning

TheGlovner · Sep 13th 2017

Re: Auto fill unknown values from available data: day, month and year.

Cool, if you need any more help obviously just say, I'm subscribed to this post now anyway.

Auto fill unknown values from available data: day, month and year.

Participate now!

Share