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

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.
--
Jimmy D


"William G" wrote:

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

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


On Jun 26, 1:03*am, William G wrote:
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

On Jun 25, 5:42*pm, Pete_UK wrote:
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

On Jun 26, 1:03*am, William G wrote:

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

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

Chip Pearson has some detailed notes about array formulae he

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

Hope this helps.

Pete

On Jun 26, 5:00*pm, William G wrote:

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