If Formula great than 7 scenarios

[William G]

I'm trying to write an 'if' formula that will sum totals across 24
columns:

example spreadsheet
Hr 1 2 3 4 5 6 etc...
6/1 10 8 18 15 7 13
6/2 2 10 15 8 5 15
6/3 14 5 15 7 10 13
etc...

We need to charge company A for the first 10 megawatts per hour per
day. Company B will get anything over 10.

for 6/1
totals should be: 55 (sum of items for the first 10wm
(10+8+10+10+7+10))
totals should be: 16 (sum of items for over 10wm (8+5+3))

Since the 'if' statement only allows 7 scenarios, what formula can sum
each column based on the 10wm limit listed above?

This is difficult to explain in a 'email', but let me know if you need
more information.

THANK YOU!!!!
William

 

[Jimmy D]

Had a similar problem with another program I was developing. Unsure if there
is a formula but I ran statements on a new worksheet and fed them off each
other to get my results. This enabled me to divide up my long string to a few
shorter ones bypassing the limitation.

 

[Pete_UK]

I based my testing on your example - you will need to adjust the cell
references to suit your real data (instead of G2 you will probably
have Y2). With the example data occupying A1:G4, I used I2 for the
company A result and J2 for the company B result. I put this array*
formula in I2:

=SUM(IF(B2:G2>=10,10,B2:G2))

and this array* formula in J2:

=SUM(IF(B2:G2>=10,B2:G2-10,0))

and got the results 55 and 16 as expected. I copied the formulae down
and got 45, 10 and 52, 12 for the other rows.

* Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the normal ENTER. If you do this correctly then Excel will
wrap curly braces around the formula when viewed in the formula bar -
do not type these yourself. If you edit the formula you will need to
use CSE again.

Hope this helps.

Pete

 

[William G]

I based my testing on your example - you will need to adjust the cell
references to suit your real data (instead of G2 you will probably
have Y2). With the example data occupying A1:G4, I used I2 for the
company A result and J2 for the company B result. I put this array*
formula in I2:

=SUM(IF(B2:G2>=10,10,B2:G2))

and this array* formula in J2:

=SUM(IF(B2:G2>=10,B2:G2-10,0))

and got the results 55 and 16 as expected. I copied the formulae down
and got 45, 10 and 52, 12 for the other rows.

* Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the normal ENTER. If you do this correctly then Excel will
wrap curly braces around the formula when viewed in the formula bar -
do not type these yourself. If you edit the formula you will need to
use CSE again.

Hope this helps.

Pete


Pete,

THANK YOU!!!!! It worked great!!!

I'm new to this CSE, where can I find more information about how that
works, when to use it, etc...?

Thanks again.
William

 

[Pete_UK]

Glad it worked for you, William - thanks for feeding back.

Chip Pearson has some detailed notes about array formulae here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this helps.

Pete