Excel 2007 : Budgeting question

[djv0001]

I would like to make it so that the Per Pay Period column (c2-c4) automatically adjusts to show the most recent pay period whenever I enter a new row into the payperiod table below(lines 8,9 etc…) Is that possible? Any other suggestions on how to make this budget more informative/useful/handy would be great.

[sweep]

Hi,

You can replace G8 in your formulas with

OFFSET(G7,COUNT(G8:G12),0)

ie C2=OFFSET(G7,COUNT(G8:G12),0)*B2


It may be a good idea to have a larger number of rows between headings and totals, in which case you should expand the range in the offset fuction, eg

OFFSET(G7,COUNT(G8:G30),0)

[DonkeyOte]

Given OFFSET is Volatile I think you would be best served setting up C such that:

C2: =LOOKUP(9.99999999999999E+307,$G$8:$G$13)*$B2
copied down for C3:C4

D2: =$G$14*$B2
copied down for D3:D4

C8: =$B8*$F$4
copied down to C13

It's not clear how you're calculating the Tax so I will ignore D8:D13.

Given G8:G13 is meant to represent periodic totals I think the formula should be:
G8: =$E8-$F8
copied down

[Steve R]

See attached workbook

[DonkeyOte]

Steve R,

my point re: Volatiles would still hold true for your approach
(though Indirect in this case)

I think if one were to pursue a Volatile approach Sweep's OFFSET would be preferable given it is a non-array approach.

Just my advice to OP based entirely on my own opinion (FWIW).

[djv0001]

Can some one explain to me what this formula is doing and how i do i modify it so it will take into account more rows?

=INDIRECT("G"&MAX(IF($A$8:$A$10<>"",ROW($A$8:$A$10),"")))*B2

[Steve R]

Can some one explain to me what this formula is doing and how i do i modify it so it will take into account more rows?

=INDIRECT("G"&MAX(IF($A$8:$A$10<>"",ROW($A$8:$A$10),"")))*B2

This formula is an array formula. You need to press Ctrl+ Shift + Enter after it is typed in the formula field.

The formula checks if any row in $A$8:$A$10 is not empty. If it is not empty it will get the largest row value of the cells that are not empty. MAX(IF($A$8:$A$10<>"",ROW($A$8:$A$10)

The formula will then get the value in column G with the same row number. At last it will multiply with B2.


If you want to take account more rows you need to edit these parts of the formula: $A$8:$A$10