Formula Q: Should be simple but got me stuck

[franklin_m]

I have a set of data, a subset provided in the mini sheet, that I want to do a calculation for the area in gray/red using a formula that I can drag across columns and rows to match the full data set. Cells E1:P1 and D2:D13 are years. Cells A2:A13 are years. Cells B2:B13 are datapoints corresponding to a value in that year.

Here's what it's doing - albeit a brute force method: For the value in F6, it takes B3 and divides by B6 (=$B$3/$B6). Similarly, for the value in N4, it takes B11 divided by B4 (=$B$11/$B4). Note that I colored cells in red where it's dividing a number by itself - which also makes for a convenient error check.

What I would like is a formula for the gray area that I can drag and fill for a period of years from 2024 back to 1914 instead of brute force.

Thanks.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[6StringJazzer]

In E2:

=XLOOKUP(E$1,$A:$A,$B:$B)/$B2

For older versions that do not have XLOOKUP:

=VLOOKUP(E$1,$A:$B,2)/$B2

Copy to fill table.

In later versions it might be possible to do this with a single array formula in E2 but I'm not sure.

Since you provided a picture instead of actual data I did not set up a sheet from scratch to test this.

 

[franklin_m]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done ... Microsoft® Excel® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20114) 64-bit

 

[franklin_m]

In E2:

=XLOOKUP(E$1,$A:$A,$B:$B)/$B2

For older versions that do not have XLOOKUP:

=VLOOKUP(E$1,$A:$B,2)/$B2

Copy to fill table.

In later versions it might be possible to do this with a single array formula in E2 but I'm not sure.

Since you provided a picture instead of actual data I did not set up a sheet from scratch to test this.

Jeff, that's perfect ... it works! I figured it'd be something simple. My experience with Xlookup is thin, seeing how it solves problems like this makes it much more valuable!

 

[Fluff]

Thanks for updating your profile.
Another option in E2 only is

=TOROW(B2:B13)/B2:B13

 

[franklin_m]

Thanks for updating your profile.
Another option in E2 only is

=TOROW(B2:B13)/B2:B13

Thanks ... btw, I went looking for thread tool to mark this is answered etc., can't seem to find it. Did I miss it?

 

[Fluff]

Glad we could help & thanks for the feedback.

We don't mark threads as answered, but you can select the tick mark to the right of one post to mark it as the solution.

 

[franklin_m]

Glad we could help & thanks for the feedback.

We don't mark threads as answered, but you can select the tick mark to the right of one post to mark it as the solution.

Thanks!

 

[Fluff]

You're welcome.