LET function not producing a spill range

**Coley356** · 11-09-2021, 02:20 PM

Can someone tell me why my formula in G4 (attached spreadsheet) is not working?

I have two Dynamic Array spill columns (E4# & F4#) and I want a third spill column (G4#) performing the following calculation:
IF
(F4# previous row)>=2.5 OR (F4# current row)>=2.5 OR (F4# next row)>=2.5 OR ((E4# previous row)-(E4# current row))<$E$1
THEN (G4# previous row)
ELSE (G4# previous row) +1

It doesn't seem that I can do an IF/OR in this scenario, so I used nested IFs instead, but it still doesn't seem to work.

**Fluff13** · 11-09-2021, 02:46 PM

You have a circular reference, this part

Formula:

Please Login or Register  to view this content.

should be -1,0 not -1,1

**Coley356** · 11-09-2021, 03:24 PM

Thank you Fluff13
is there any way to get the previous cell in this case? without a helper column?

**Fluff13** · 11-09-2021, 03:41 PM

I miss-understood what your formula was doing. I don't think you can do it without a helper column, otherwise you get a circular reference.

**Coley356** · 11-09-2021, 06:01 PM

Can you offer any help with the helper column for my previous post example?

Or maybe i can figure it out if you can tell me why this doesn't work and how to make it work.
=COUNT(INDIRECT("R7C"&COLUMN(GS7#)&":R"&ROW(GS7#)&"C"&COLUMN(GS7#),FALSE))

For another data set, I am doing a similar calculation as my first post example. for this one I went ahead and added a helper column (GS7#). in the helper: IF criteria is met, THEN 1, ELSE ""
then I figured in my final column (GT) I would do a count on a range from the first cell in GS7# to the active cell in GS7#. should end up looking like table below, but it doesnt work with my dynamic row reference.

	criteria	GS	GT
R7	true	1	1
R8			1
R9	true	1	2
R10			2
R11			2
R12	true	1	3
R13	true	1	4
R14			4
R15			4

**Fluff13** · 11-10-2021, 08:59 AM

I'm afraid I have no idea how to do this within a spill range.

**Fluff13** · 11-10-2021, 10:01 AM

On further reflection, you could use something like

Formula:

Please Login or Register  to view this content.

**Coley356** · 11-10-2021, 03:36 PM

I could not seem to get =SUBTOTAL(9,OFFSET(G4#,0,0,SEQUENCE(ROWS(G4#)))) to work so I ended up using a workaround.

Thanks again for all your help.

**Fluff13** · 11-10-2021, 03:44 PM

Glad you sorted it & thanks for the feedback.

LET function not producing a spill range

LinkBack

Thread Tools

Rate This Thread

Display

LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Re: LET function not producing a spill range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Spill Range Across Rows and Columns

[SOLVED] #spill! error using Filter function

[SOLVED] Removing #spill! and then #N/A from the range

Spilled Formulas That Don't Spill Zero Values and Spill A Total?

[SOLVED] Using the =IF function is ok on single cell but gives#SPILL on a range

[SOLVED] sumif producing SPILL error

UDF For spill range(implicit intersection)

Bookmarks

Bookmarks

Posting Permissions