Populate Weekly, Fortnightly, Monthly and Annually Cells automatically?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I am trying to create a budget spreadsheet with 4 columns showing the
cost Weekly, Fortnightly, Monthly and Annually.



Weekly, Fortnightly, Monthly, Annually.
10 20 43.33 520



Ideally I'd like to be able to enter the cost into any one of the 4
columns, and have the others automatically display the resulting cost
for that time period.

But I am having no luck. I have tried entering the 'amount' into a 5th
column together with a combobox where I can select the relevant 'time
period', but I can't work out how to get any further?


I think I need something like the following for the Weekly column.

if 'time period' is 'weekly' then 'amount' *1, but if 'time period' is
'fortnightly' then 'amount' /2. etc.



Cheers.
 
I am trying to create a budget spreadsheet with 4 columns showing the
cost Weekly, Fortnightly, Monthly and Annually.

Weekly,    Fortnightly,    Monthly,    Annually.
  10            20          43.33        520

Ideally I'd like to be able to enter the cost into any one of the 4
columns, and have the others automatically display the resulting cost
for that time period.

But I am having no luck. I have tried entering the 'amount' into a 5th
column together with a combobox where I can select the relevant 'time
period', but I can't work out how to get any further?

I think I need something like the following for the Weekly column.

if 'time period' is 'weekly' then 'amount' *1, but if 'time period' is
'fortnightly' then 'amount' /2. etc.

Cheers.
Click to expand...

Hi Bob,

I'm sure there is a 'nicer' way to do this but right now i cant think
of one. In the meantime try this
I have used two additional columns, one for the value and one for
either W,F,M, or A depending on the time frame, you can use a drop
down validation box to get the same results .
I have your time headings in Col A1:D1, the budget amount in E2, and
the requested time frame in F2, the formulas are entered in a2
onwards.

in your weekly column enter this
=IF(F2="W",E2,IF(F2="F",E2/2,IF(F2="M",(E2*12)/52,IF(F2="A",E2/52))))

fortnightly

=IF(F2="W",E2/2,IF(F2="F",E2,IF(F2="M",(E2*12)/26,IF(F2="A",E2/26))))

monthly

=IF(F2="W",(E2*52)/12,IF(F2="F",(E2*26)/12,IF(F2="M",E2,IF
(F2="A",E2/12))))

Annually

=IF(F2="W",E2*52,IF(F2="F",E2*26,IF(F2="M",E2*12,IF(F2="A",E2))))

Like i said I'm sure there may be a cleaner solution, but this seems
to do the job

Regards

David
 
Hi Bob,

I'm sure there is a 'nicer' way to do this but right now i cant think
of one. In the meantime try this
I have used two additional columns, one for the value and one for
either W,F,M, or A depending on the time frame, you can use a drop
down validation box to get the same results .
I have your time headings in Col A1:D1, the budget amount in E2, and
the requested time frame in F2, the formulas are entered in a2
onwards.

in your weekly column enter this
=IF(F2="W",E2,IF(F2="F",E2/2,IF(F2="M",(E2*12)/52,IF(F2="A",E2/52))))

fortnightly

=IF(F2="W",E2/2,IF(F2="F",E2,IF(F2="M",(E2*12)/26,IF(F2="A",E2/26))))

monthly

=IF(F2="W",(E2*52)/12,IF(F2="F",(E2*26)/12,IF(F2="M",E2,IF
(F2="A",E2/12))))

Annually

=IF(F2="W",E2*52,IF(F2="F",E2*26,IF(F2="M",E2*12,IF(F2="A",E2))))
Click to expand...

Hi David,

That has work perfectly. Thanks very much for taking the time to help me
out.

Cheers.
 
Back
Top