Re: Moving array formulas

From: Max (demechanik_at_yahoo.com)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 18:29:34 +0800

One way ..

Put in A1:

=IF(INDIRECT("B"&(INT((ROW(A1)+5)/6)-1)*6+2)=INDIRECT("H"&INT((ROW(A1)+5)/6)
+2),INDIRECT("F"&INT((ROW(A1)+5)/6)+2),"")

(just press ENTER, it's not an array formula)

Copy A1 down as desired,
but to terminate at a multiple of 6 rows
e.g. at: A6, A12, A18, A24, A30, etc

The above should return the results that you're after ..

Note that the formula will return blanks: ""
as the value_if_false,
instead of the value: FALSE
(thought "blanks" was a cleaner output to have)

You could also suppress* extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

*if you're copying down ahead of data input in cols B, H & F 

--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Anthony Slater" <AnthonySlater@discussions.microsoft.com> wrote in message
news:47AA18E0-1285-496C-AE89-34ECEAF9B202@microsoft.com...
> I have the following formular that is working. However, it needs to be the
> same for 6 rows but then moving the references accordingly.  B needs to be
> increased by 6 every 6 rows. H & F need to be increased by 1 every 6 rows
> (see below for how it needs to be)
>
> ie
> Rows A1 to A6
> {=if($B$2=H3,F3)}
>
> Rows A7 to A12
> {=if($B$8=H4,F4)}
>
> Rows A13 to A18
> {=if($B$14=H5,F5)}
>
>
> How can I acheive this?

Relevant Pages

  • Re: GET STRING(S) LIST(A);
    ... The point is that the specification for LIST directed input specifies that the data items are *speparted* by commas (possibly surrounded by blanks) or by one or more blanks. ... End or record and end of file both terminate an item. ... While the manual does not explicitly say so that I can find, I would assume by analogy that end of string also terminates an item and experiment confirms this. ... Note that since the 8 in the first line is negative, the ERROR condition was raised by the first GET statement upon attempting to read beyond the end of the string. ...
    (comp.lang.pl1)
  • Re: remove blank cells - no sorting
    ... Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, ... cells that read row B. ... blanks in Col_A to write over values in Col_B. ... From the Excel Main Menu: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Any way to take a word as input from stdin ?
    ... I searched the c.l.c archives provided by Google as Google Groups ... Does it terminate on ... blanks, on blanks and non-print chars, on blanks and tabs, etc. ... what the function extracts. ...
    (comp.lang.c)
  • Re: Removing Blanks
    ... Valko" wrote: ... The formula in the yellow cell counts how many rows meet the criteria. ... This could be done with an array formula but it would be calculation ... with blanks throughout the list and I end up manually moving the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Removing Blanks
    ... The formula in the yellow cell counts how many rows meet the criteria. ... Microsoft Excel MVP ... This could be done with an array formula but it would be calculation ... with blanks throughout the list and I end up manually moving the ...
    (microsoft.public.excel.worksheet.functions)
Loading