• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formula stops working

Mike H..

Mike H..

Active Member
Please see the attached workbook. The array formula in Col B is working exactly as expected.

The same formula in column C is wrapped in an IF statement so it only evaluates on certain rows but wrapping it in IF stops it working and I have no idea why. Any explanation? Am I making as silly mistake?
 

Attachments

  • addup.xlsx
    9.2 KB · Views: 14
Mike,

The difference is that in the later formula the INDIRECT function is not taking it as a range INDIRECT({"A1:A5"}) where as without IF the INDIRECT fetches INDIRECT("A1:A5")...

so far this much understood but still looking for solution.
 
Faseeh said:
Mike,

The difference is that in the later formula the INDIRECT function is not taking it as a range INDIRECT({"A1:A5"}) where as without IF the INDIRECT fetches INDIRECT("A1:A5")...

so far this much understood but still looking for solution.
Click to expand...
Hi,

I agree completely, when you evaluate the 2 formula then for the formula without the IF statement you see this

=SUM(INDIRECT("A1:A5"))

But within the IF statement it evaluates to this with the additional { }

=SUM(INDIRECT({"A1:A5"}))

What I find most confusing is that both the above entered as a worksheet formula work but if you wrap the second formula in an IF statement it doesn't work.

works
=IF(A1="",SUM(INDIRECT("A1:A5")),"")

doesn't work
=IF(A1="",SUM(INDIRECT({"A1:A5"})),"")
 
If you are really interested then you can wait till someone explains it. Here is an alternate:

=IF(A1="",SUM(OFFSET(A1,0,0,SMALL(IF(A1:A14="",ROW($A$1:$A$14)),2))),"")

with CSE.

..Entered in C1 and dragged down.
 
Faseeh said:
If you are really interested then you can wait till someone explains it. Here is an alternate:

=IF(A1="",SUM(OFFSET(A1,0,0,SMALL(IF(A1:A14="",ROW($A$1:$A$14)),2))),"")

with CSE.
Click to expand...
Faseeh,

Thanks for that but I know how to do it with OFFSET, I'm trying to understand why simply wrapping a formula in IF stops that formula working.
 
Asheesh said:
Hi Mike -

Try this...it works fine..

IF(A1="",SUM(INDIRECT("A" & ROWS($A$1:A1) &":A" & MIN(IF(A2:$A$15="",ROW(A2:$A$15),"")))),"")
Click to expand...
Hi,

That's bizarre that that works, the only difference is ROWS($A$1:A1) as opposed to ROW(). I think I'll just have to accept that it works without understanding why my version didn't.

Thanks.
 
Hi Mike ,

Since you have identified the reason for the problem , I don't think you need any more explanations ! So , the real point is , how do we convert an array reference to a scalar reference ?

Try this :

=IF(A1="",SUM(INDIRECT("A" & SUMPRODUCT(ROW()) &":A" & MIN(IF(A2:$A$15="",ROW(A2:$A$15),"")))),"")

Narayan
 
Hi ,

I just saw Asheesh's formula , and it does the same thing ! ROWS outputs a scalar value , whereas ROW outputs an array when it is wrapped inside an IF and the entire formula is entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
ok must have posted around same time as above as i didnt see you got a solution .... cool didnt think of rows ... rather than row



ok im pretty new to excel but have being teaching myself ... after looking at your formula and going through it ... it seems fine but when i put it through the evaulator i can see it dosent evaluate the same way as when there is no if ... in fact brackets are added and just before end of evaluation it shows #value error
so im lost as to why ... need someone with more experience than me to explain that ...
but i did try a few things to see if i could get it to do what you wanted ... tryed changing if argument but to no avail .... then looked at other options and amazingly the below works ... changed the if to a ... AND ... with two criteria and it works ..... you just have to accept a 0 in the cells that should be blank
=AND(A1="",B1=B1)*(SUM(INDIRECT("A"&ROW()&":A"&MIN(IF(A2:$A$15="",ROW(A2:$A$15),"")))))

other option i considered if this hadnt worked out was to write a macro (only started VBA a few days ago so this probably beyond me)
would have checked cell with a if and then if blank got it to use your non if formula .... doing it this way you would not need a formula with a if in it.

anyway hope this helps .... (above formula still requires you to ctrl shift and enter )
 
Last edited:
You must log in or register to reply here.
Back
Top