Mapping cells between between two ranges

0 votes

I need to translate the values in one data range to the values in the other range because I have two data ranges. I'm probably doing a terrible job of describing this, but here's an illustration:

I have a range with standardized residuals (Range1):

1.7 0.6 -3.6
-1.5 -0.8 3.6
-0.2 0.2

And a second set of standardized residuals (Range2)

-2.6 1.7 0.7
1.5 -0.7 -0.7
1.2 -1.2

I take a bootstrapped sample from Range1 and get this output (BS1).

0.6 -3.6 -1.5 3.6
-0.2 0.6 -0.2
-0.2 -3.6
1.7

Now I want to use BS1 to get a bootstrapped sample of Range2 (BS2) by using the location of each BS1 value in Range1.

For example. BS1(1,1) = 0.6. That corresponds to Range1(1,2). So, BS2(1,1) = Range2(1,2) = 1.7. I want to end up with this table, BS2.

1.7 0.7 1.5 -0.7
1.2 1.7 1.2
1.2 0.7
-2.6

Can someone come up with a way to do this using formulas in Excel? VBA won't work for this. I'm hoping that makes things a little bit easier since, despite how unlikely it may sound, neither Range1 nor Range2 will have duplicate values.

Mar 27, 2023 in Others by Kithuzzz
• 38,010 points
550 views

1 answer to this question.

0 votes

Try this:

=IF(B4:H10<>"",B14:H20,"")

Sample illustration

answered Mar 27, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to give space between two Textfield in Flutter?

Hi@akhtar, There is various way to give space ...READ MORE

answered Sep 8, 2020 in Others by MD
• 95,440 points
4,208 views
0 votes
1 answer

Calculate time difference in hours between two dates and times

Simply subtract the two dates to get ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
1,292 views
0 votes
1 answer

SQL query to select dates between two dates

you should put those two dates between ...READ MORE

answered Nov 7, 2022 in Others by gaurav
• 23,260 points
2,513 views
0 votes
1 answer

Excel VLOOKUP between two sheets failing

Try this: =VLOOKUP(A2,'1'!$A$2:$E$811,5,0) I altered "1"! $A$2:$A$811 to '1'! ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,420 points
429 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,420 points
622 views
0 votes
1 answer

Substring/Find last occurence of "/" and output everything to the right of it

Use TEXTAFTER() Function: =TEXTAFTER(B2,"/",-1) READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
319 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
755 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,400 views
0 votes
1 answer

Count the sum of difference between two cells

Use a for loop: Sub AbsoluteDifference() ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,420 points
204 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
1,206 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP