too many arguments I think

[marksuza]

Hi, I am new to the forum and not too experienced in Excel. I wrote
this argument that does a calculation if a cell in the C column is 0.
IF(C10=0,H9^(B1/A1),IF(C11=0,SUM(H9:H10)^(B1/A1),IF(C12=0,SUM(H9:H11)^(B1/A1),IF(C13=0,SUM(H9:H12)^(B1/A1),IF(C14=0,SUM(H9:H13)^(B1/A1),IF(C15=0,SUM(H9:H14)^(B1/A1),IF(C16=0,SUM(H9:H15)^(B1/A1),
IF(C17=0, SUM(H9:H16)^(B1/A1), IF(C18=0, sum(H9:H17)^(B1/A1), IF(C19=0,
sum(H9:H18)^(B1/A1), IF(C20=0, sum(H9:H19)^(B1/A1),
sum(H9:H20)^(B1/A1)))))))))))), but when it gets to C17 it gives an
error, it highlights the 'sum' argument, I think I have put too many
arguments. How can I solve this? I dont know how to put in vb if that
is the only option. Thanks

 

[Leo Heuser]

Hi

Yes, Excel can only work with a max. of 7
embedded functions.

Instead try this array formula. Replace C30 to reflect
the actual situation.
If more than one instance of a zero is found in C10:C30,
the one with the smallest row number will be used.

=SUM(OFFSET(H9,0,0,MIN(IF(C10:C30=0,ROW(C10:C30)-ROW(C10)+1))))^(B1/A1)

The formula must be entered with <Shift><Ctrl><Enter> instead
of <Enter>, also if edited later. If done correctly, Excel will display
the formula in the formula bar , enclosed in curly brackets { } Don't
enter these brackets yourself. They are Excels way of showing,
that the formula is an array formula.

 

[Ron Rosenfeld]

Hi, I am new to the forum and not too experienced in Excel. I wrote
this argument that does a calculation if a cell in the C column is 0.
IF(C10=0,H9^(B1/A1),IF(C11=0,SUM(H9:H10)^(B1/A1),IF(C12=0,SUM(H9:H11)^(B1/A1),IF(C13=0,SUM(H9:H12)^(B1/A1),IF(C14=0,SUM(H9:H13)^(B1/A1),IF(C15=0,SUM(H9:H14)^(B1/A1),IF(C16=0,SUM(H9:H15)^(B1/A1),
IF(C17=0, SUM(H9:H16)^(B1/A1), IF(C18=0, sum(H9:H17)^(B1/A1), IF(C19=0,
sum(H9:H18)^(B1/A1), IF(C20=0, sum(H9:H19)^(B1/A1),
sum(H9:H20)^(B1/A1)))))))))))), but when it gets to C17 it gives an
error, it highlights the 'sum' argument, I think I have put too many
arguments. How can I solve this? I dont know how to put in vb if that
is the only option. Thanks

Depending on exactly what is in column C, the following formula will work, or
at least get you pointed in a usable direction:

=SUM(INDIRECT("H9:"&ADDRESS(MATCH(0,C9:C100,0)+7,8)))^(B1/A1)

The MATCH argument computes the row number of the entry in Column C that
contains a zero. The formula assumes that the zero in which you are interested
is the first zero in the column.

If you need to find the last zero in the column, then the array formula:

=MAX(ISNUMBER(C1:C100)*(C1:C100=0)*ROW(C1:C100))

would compute that row number. Putting it into the above formula in place of
the MATCH function, and making some changes to simplify:


=SUM(INDIRECT("H9:"&ADDRESS(MAX(ISNUMBER(C10:C100)*(C10:C100=0)*ROW(C10:C100))-1,8)))^(B1/A1)

This formula must be *array-entered*. To array-enter, hold down <ctrl><shift>
while hitting <enter>. XL will place braces {...} around the formula.



--ron