XLOOKUP & MIN

kivikatz

New Member
Joined
Sep 12, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi All. I am having some issues in returning a value with the following formula. I am trying to return the cell reference ($C$1:$AZ$1) using XLOOKUP based on a 50% target increase ($A$1) after hitting the lowest point in a range (MIN(C2:AZ2)). The current formula I am using will return a blank cell if the 50% target in C2:AZ2 is reached before the lowest point is reached.

IF(XLOOKUP(MIN((C2:AZ2))*(1-$A$1),C2:BJ2,$C$1:$AZ$1,"",1)<XLOOKUP(MIN((C2:AZ2)),C2:AZ2,$C$1:$AZ$1,"",1),"",XLOOKUP(MIN((C2:BJ2))*(1-$A$1),C2:AZ2,$C$1:$AZ$1,"",1))

I'm sure there is a way of calculating this, but it is beyond my expertise (or lack thereof). I appreciate any help in solving this issue, including a better formula/function to find the relevant cell reference.

Below is a screenshot for better reference. Cell A2 calculation should ignore any 50% target increase reached before the MIN(C2:AZ2) is reached (I2) and only calculate 50% target increase reached after the MIN(C2:AZ2) is reached. The formula should return Q1 (25).

Hope you understand what I am trying to accomplish. Thanks.

Screen Shot 2020-09-12 at 1.59.01 PM.png
.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try this. I have assumed that the numbers in row 2 will always be negative since that is the case with your example.
If that is not the case, or this does not meet your need, can you posts a few more rows of sample data (with XL2BB) and include the expected results, manually entered & include any further clarification that you can?

kivikatz 1.xlsm
ABCDEFGHIJKLMNOPQRS
150%12131415161718192021222324252627
225-0.22-0.29-0.58-0.58-0.59-0.58-0.6-0.59-0.42-0.45-0.42-0.39-0.36-0.32-0.28-0.52-0.5
Sheet1
Cell Formulas
RangeFormula
A2A2=INDEX(FILTER(C1:AZ1,(COLUMN(C1:AZ1)>MATCH(MIN(C2:AZ2),C2:AZ2,0)+COLUMN(C2)-1)*(C2:AZ2>=A1*MIN(C2:AZ2))),1)
 

kivikatz

New Member
Joined
Sep 12, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thank you Peter. That works really well. And thank you for the advice on XL2BB. I am attaching a screenshot with with my attempt at the formula using positive numbers based on a 50% target decrease ($A$1) after hitting the highest point in a range (the reverse of what I originally requested help on). For example, based on a high of 0.43 in Row 2, it returns 20 from Row 1. I think the formula works, but would appreciate any feedback.

I would also like to see if I can add a formula that returns the relevant cell reference in Row 1 based on a target (e.g. 0.40) being reached in row 2. I tried experimenting with Index/Match function, but had no success. In the example, for Row 2, it should return 10 from Row 1, since the target was reached before the preceding formula is triggered. Hope my screenshot explains what I am trying to do. Thanks again.

Xlookup Example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
150%Target12345678910111213141516171819202122232425
2200.40-0.06-0.04-0.04-0.04-0.020.270.310.350.380.400.420.420.420.430.270.250.250.250.230.200.170.130.090.090.09
3160.750.580.590.600.610.600.600.590.590.590.590.580.560.530.390.340.300.300.300.250.200.110.060.060.060.02
4
5High
6Trigger
Sheet2
Cell Formulas
RangeFormula
A2:A3A2=IFERROR(INDEX(FILTER($C$1:$AA$1,(COLUMN($C$1:$AA$1)>MATCH(MAX(C2:AA2),C2:AA2,0)+COLUMN(C2)-1)*(C2:AA2<=(1-$A$1)*MAX(C2:AA2))),1),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I am attaching a screenshot with with my attempt at the formula using positive numbers based on a 50% target decrease ($A$1) after hitting the highest point in a range
I'm confused by the description and sample data. For example ..
  • You say using positive numbers but there is a mix of positive and negative numbers. Can you clarify?
  • You have marked blue as "High", yet F3 is higher than G3 that you have marked blue. Can you clarify?
  • Numbers in columns C:AA are showing 2 decimal places yet the actual numbers contain more decimal places. Should we be considering the shown value or the underlying value in those cells?
  • Why have you marked S3 as the trigger in row 3 and not R3 (before) or T3 (after) when all 3 cells contain the same value (0.30 or underlying 0.2977)?
 

kivikatz

New Member
Joined
Sep 12, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the reply. Sorry for some of the errors. I was staring at the screen for so long that I was a bit sloppy in highlighting some of the cells. Guess I need glasses. Here are my clarifications for your questions.

  • You say using positive numbers but there is a mix of positive and negative numbers. Can you clarify?
    • I want the formula to reflect a target decrease from the highest point in the range. The range can include both positive an negative numbers. I experimented with the formula you originally provided, and I think it works, but would like confirmation.
  • You have marked blue as "High", yet F3 is higher than G3 that you have marked blue. Can you clarify?
    • Apologies for the sloppiness. It should have been F3.
  • Numbers in columns C:AA are showing 2 decimal places yet the actual numbers contain more decimal places. Should we be considering the shown value or the underlying value in those cells?
    • I would like to consider the underlying value. I show only 2 decimals just to make the presentation more readable.
  • Why have you marked S3 as the trigger in row 3 and not R3 (before) or T3 (after) when all 3 cells contain the same value (0.30 or underlying 0.2977)?
    • Apologies again for the sloppiness. It should have been R3, the first cell in the range that would trigger the formula (50% off of 0.61 high in F3).
Thanks again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Thanks for the clarifications. Your A2:A3 formula looks fine to me.

For the new question, try this for row 2, & copied down. I'm not sure that I have interpreted what you want correctly though?
Excel Formula:
=INDEX(FILTER(C$1:AA$1,(C2:AA2>=B2)*(SEQUENCE(,COLUMNS(C2:AA2))<MATCH(A2,C$1:AA$1,0)),"N/A"),1)
 

kivikatz

New Member
Joined
Sep 12, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks a lot. Looks like its working out as intended.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

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
Top