Problem with reference cell when repeating formula down column

Young Grasshopper

Board Regular
Joined
Dec 9, 2022
Messages
58
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello world!

I have this formula:
=LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)
This formula gives three random values from G11:U11 as long as the corresponding helping row, H1:V1, H2:V2 etc, is bigger den 0.
(Random since the values in H1:V1 is generated from fromula; =IF(LEN(G11)=0,0,RAND()

I'm trying to copy this formula down a column for about 100 rows, but the H:V ranges get a little messed up when copying the formula down. Right now it would go;
1+H1:V1,1+H2:V2,1+H3:V3
1+H2:V2, 1+H3:V3,1+H4:V4
1+H3:V3, 1+H4:V4,1+H5:V5


But need this formula to go;
(Row1) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)

(Row2) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H4:V4)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H5:V5)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H6:V6)),G12:U12)

(Row3) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H7:V7)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H8:V8)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H9:V9)),G13:U13)

I understand that this is just how excel would calculate this logically, but is there any way to control this or rewrite the code in a different way?
Or is it just better to do all 100 lines manually?

Would appreciate any help:)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Let's say that you are going to enter the formula in X1, and then copy down. Try...

Excel Formula:
=LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$1:$V$1,ROWS(X$1:X1)*3-3,0))),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$2:$V$2,ROWS(X$1:X1)*3-3,0))),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+OFFSET($H$3:$V$3,ROWS(X$1:X1)*3-3,0))),G11:U11)

Alternatively, I think you can probably use the following instead...

Excel Formula:
=TEXTJOIN(" | ",FALSE,INDEX(G11:U11,RANDARRAY(,3,1,COLUMNS(G11:U11))))

Hope this helps!
 
Upvote 0
Solution
My man! Works like a dream:D

The last formula is also a big time saver, but the only problem is that it includes cells in G11:U11 that are blank, which is the sole reason i use the other formula.
Is there a quick tweak that would make the last code only include nonblanks in the range?
 
Upvote 0
Does this return the desired result in all circumstances (ie. less than 3 values available in G11:U11) ?

Excel Formula:
=LET(data,FILTER(G11:U11,LEN(G11:U11)>0,#N/A),TEXTJOIN(" | ",TRUE,INDEX(data,RANDARRAY(,MIN(3,COLUMNS(data)),1,COLUMNS(data),TRUE))))
 
Upvote 0
To make the situation any easier I need formulas that work in most versions of Excel, and FILTER is only in 365 i think?
Anyway, your first fomula works great together with my formula, so I'll just keep it like this:)

You're a lifesaver, Domenic! Enjoy the rest of your day!:)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top