Understanding Array-Entered Formulas

[JackDanIce]

Hi,

First time I've used array-entered formulas and struggling to work out what's wrong with this formula:

{=SUM(IF(LEFT(A1:A6,2)="sd",VALUE(RIGHT(A1:A6,LEN(A1:A6)-2)),IF(ISNUMBER(VALUE(A1:A6)),A1:A6,"")))}

To explain, I have a list of values in fixed range A1:A6. These values are either numerical or alphanumeric. If they're alphanumerica, they will begin with "SD" or some other characters, but each cell value will always end in numerical characters

I want to add all these values up in a single cell (C1). I could use the B column to do the above on each single cell (e.g. A1 then copy down to A6) and use sum(B1:B6) but I'm trying to learn how to use array-enetered formulae and not do this!

Any help please?
Thanks,

Jack

 

[JackDanIce]

Example of A1:A6 data is:

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=99 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=99 height=17>SD1.4568</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>

1.48​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SD2.456</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>sd2.457</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>sd2.458</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABC6.98</TD></TR></TBODY></TABLE>

 

[tusharm]

Use Excel's Evaluate Formula capability to understand what is happening to the array formula. See the 1st link at
Array Formulas
http://www.tushar-mehta.com/publish_train/xl_vba_cases/array_formulas.htm

 

[JackDanIce]

Thank you Tusharm,

That's helped me understand that my formula is only checking the conditions for each formula against cell A1 (e.g. a single cell), but I don't know how to adapt the formula so that it tests for every cell in the range A1:A6

 

[tusharm]

Worked fine in my quick test a few minutes ago.

 

[JackDanIce]

Ok, that's really annoying as I've just been googling and trying to find more info on this!!

I'm typing the formula as below without the curly brackets and then pressing CRTL+SHIFT+ENTER and it's not working! Spreadsheet is set to autocalculate and I'm hitting F9 anyway, grrrr annoying!

Will percievere (however you spell that word)