If than Else Trouble
 

I need help with a spreadsheet that is used to calculate a companies
forecast. How this works is during a month a spreadsheet calculates the
hours worked to get a financial forecast.We put on there planned weeks hours
and than fill in at the end of the week the actual hours.

This is a spreadsheet where you add the actual columns (these are the weeks
that have happened) that occurred with the forecasted columns. (These are
the columns that have not occurred.

(Example)
If the 1a is filled and is the only (a) column filled than you calculate the
1a+2+3+4 columns
If the 1a and the 2a is filled than you calculate the 1a+2a+3+4 columns
If the 1a and the 2a and the 3a is filled than you calculate the 1a+2a+3a+4
columns


Attempted with no success below. I tried to work backwards to see what week
we were in and than calculate accordingly.
----------------------------------------------------------------
Column 1, 1a, 2, 2a, 3, 3a, 4, 4a

If column 4a 0 than add column 4a+3a+2a+1a

else add column 1a+2a+3a+4

If than Else Trouble
 

One way ..

A sample construct is available at:
http://www.savefile.com/files/1481398
Sum_AlternateCols_Goofy_wks.xls

Assuming the cols: 1, 1a, 2, 2a ... 4a are cols A to H
with data from row2 down

Put in say, the formula bar for I2:
=SUM(IF(--(MOD(COLUMN(A2:H2),2)=0),A2:H2))
+IF(ISNA(MATCH(1,--(A2:H2=""),0)),0,
SUM(OFFSET(H2,,,,MATCH(1,--(A2:H2=""),0)-10)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
Copy I2 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Goofy" wrote in message
...
I need help with a spreadsheet that is used to calculate a companies
forecast. How this works is during a month a spreadsheet calculates the
hours worked to get a financial forecast.We put on there planned weeks
hours
and than fill in at the end of the week the actual hours.

This is a spreadsheet where you add the actual columns (these are the
weeks
that have happened) that occurred with the forecasted columns. (These are
the columns that have not occurred.

(Example)
If the 1a is filled and is the only (a) column filled than you calculate
the
1a+2+3+4 columns
If the 1a and the 2a is filled than you calculate the 1a+2a+3+4 columns
If the 1a and the 2a and the 3a is filled than you calculate the
1a+2a+3a+4
columns


Attempted with no success below. I tried to work backwards to see what
week
we were in and than calculate accordingly.
----------------------------------------------------------------
Column 1, 1a, 2, 2a, 3, 3a, 4, 4a

If column 4a 0 than add column 4a+3a+2a+1a

else add column 1a+2a+3a+4

If than Else Trouble
 

Hi

One way, though perhaps not the most elegant
=SUM(A2:H2)-SUM(A2*(B20),C2*(D20),E2*(F20),G2*(H20))

--
Regards

Roger Govier


"Goofy" wrote in message
...
I need help with a spreadsheet that is used to calculate a companies
forecast. How this works is during a month a spreadsheet calculates
the
hours worked to get a financial forecast.We put on there planned weeks
hours
and than fill in at the end of the week the actual hours.

This is a spreadsheet where you add the actual columns (these are the
weeks
that have happened) that occurred with the forecasted columns. (These
are
the columns that have not occurred.

(Example)
If the 1a is filled and is the only (a) column filled than you
calculate the
1a+2+3+4 columns
If the 1a and the 2a is filled than you calculate the 1a+2a+3+4
columns
If the 1a and the 2a and the 3a is filled than you calculate the
1a+2a+3a+4
columns


Attempted with no success below. I tried to work backwards to see what
week
we were in and than calculate accordingly.
----------------------------------------------------------------
Column 1, 1a, 2, 2a, 3, 3a, 4, 4a

If column 4a 0 than add column 4a+3a+2a+1a

else add column 1a+2a+3a+4