(Semi-)Automatic cell selection for calculations

[JK]

I have a data set for a range companies spanning years 1998-2004. However, some values for 2004 are not yet available. Is there an expression that means a formula will automatically use the final 3 figures from all the years i.e. detect whether the 2004 values have been input yet, and if not, use the figures from 2003 back instead
Any help gratefully received!

 

[Frank Kabel]

Hi
you may use a combination of OFFSET and COUNTA. Can you provide some
example rows (as plain text) of your spreadsheet

 

[JK]

Thanks for your reply...here are some example rows. I need to be able to perform various calcs using the final 3 numbers for each company. i.e. company A uses figures from 02, 03 & 04 while Comp B used 01, 02 & 03 etc. Cheers for any further responses!

Sales Figures
1998 1999 2000 2001 2002 2003 2004
Company A 1 3 5 7 9 5 7
Company B 1 2 3 4 7 8
Company C 4 6 8 7 6 3
Company D 2 7 8 6 4 7 8
Company E 7 6 8 2 4 7

 

[ElsiePOA]

One way:

Assuming "Company A" is in A2 and the years are in columns B2 thru H2.
In I2, put the following formula:
=IF(H2<>"",H2+G2+F2,G2+F2+E2). Copy the formula down into all cells i
column I

 

[Frank Kabel]

Hi
Assuming that the formula resides in column I enter the following in I2
=SUM(OFFSET(B2,COUNTA(B2:H2)-3,0,1,3))
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks for your reply...here are some example rows. I need to be

able to perform various calcs using the final 3 numbers for each
company. i.e. company A uses figures from 02, 03 & 04 while Comp B used
01, 02 & 03 etc. Cheers for any further responses!

 

[AlfD]

Depends on what various calcs you want to do.

If it was simply a sum or average, the
=IF(G3="",AVERAGE(D3:F3),AVERAGE(E3:G3)) or the same thing with su
would deal with it. I assume that only the last item could be absent.

If you want to treat each company's last three "results" as equivalent
you could actually copy them into three new columns and use these fo
the calcs. As new results are entered, obviously you would want th
calcs to pick them up seamlessly.

An approach along the following lines would do the copies. Assume you
2004 column is G, that your first row of data is row 3 and that yo
have added three columns X,Y and Z.

Then cell X3 could hold =if($G3<>"",E3,D3)

This will copy down as many rows as you have and it will copy acros
cols Y & Z.

I'd like to think there's a more elegant way but I don't know one.

Al