Formula Accuracy

[Bedford]

With this formula; =LET(s,SEQUENCE(6),IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*(s-1),s,,-1)),0)), when D18 is set to 6920 which is an equal measure of 1730 and should result in the number 4, however it's resulting in the number 5? If I modify up to 1731 it results with the number 4 until D18 is set to 6924 then resulting in 5 which doesn't help with having it calculate accurately. The formula I'm hoping should return 4 when D18 is 6920, return 5 when D18 is 6921.
Help please.
Thank you.

 

[Jeffrey Mahoney]

Bedford,

I broke down the formula a little at a time.

In this line I converted SEQUENCE(6) into it's new array and replaced the variable "s" with that array
=LET(s,{1;2;3;4;5;6},IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*({1;2;3;4;5;6}-1),{1;2;3;4;5;6},,-1)),0))

In this line I calculated the result of 1730*(s-1)
=LET(s,{1;2;3;4;5;6},IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,{0;1730;3460;5190;6920;8650},{1;2;3;4;5;6},,-1)),0))

The result of the XLookup should be 5 because D18 = 6920. If you want the result to be 4, you're going to need to use this formula:
=LET(s,SEQUENCE(6),IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*(s-1),s-1,,-1)),0))

or change the Sequence portion so that it is like this: SEQUENCE(5,,0) and not subtract one from each the sequence array.

 

[Bedford]

Bedford,

I broke down the formula a little at a time.

In this line I converted SEQUENCE(6) into it's new array and replaced the variable "s" with that array
=LET(s,{1;2;3;4;5;6},IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*({1;2;3;4;5;6}-1),{1;2;3;4;5;6},,-1)),0))

In this line I calculated the result of 1730*(s-1)
=LET(s,{1;2;3;4;5;6},IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,{0;1730;3460;5190;6920;8650},{1;2;3;4;5;6},,-1)),0))

The result of the XLookup should be 5 because D18 = 6920. If you want the result to be 4, you're going to need to use this formula:
=LET(s,SEQUENCE(6),IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*(s-1),s-1,,-1)),0))

or change the Sequence portion so that it is like this: SEQUENCE(5,,0) and not subtract one from each the sequence array.

Hi Jeffrey, thank you for responding. So I've entered all 3 of your suggestions, one by one, with 6920 in D18. #1&#2 returned 5, but it should be 4<=6920, #3 returned 4 correctly, but when D18 was dropped down to 6919 it returned 3, but also should have been 4<=6920. I also tried changing the sequence to; SEQUENCE(5,,0) but it too returned 3 at 6919. Very confusing.
Thank you.

 

[Jeffrey Mahoney]

Please try this
=LET(s,SEQUENCE(1,10,0)*1730,IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,s,SEQUENCE(1,10,0),-999,-1)),0))

 

[Bedford]

Please try this
=LET(s,SEQUENCE(1,10,0)*1730,IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,s,SEQUENCE(1,10,0),-999,-1)),0))

So for some reason it still returns 3 when <6920, for example 6919, but it is now returning 4 when <=6920.

 

[Jeffrey Mahoney]

So this controls how you're going to return your numbers:
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, -1 ) returns the next lower
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, 1 ) returns the next highest
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, 0 ) returns the exact match

Play with that a little

 

[Bedford]

So this controls how you're going to return your numbers:
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, -1 ) returns the next lower
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, 1 ) returns the next highest
XLOOKUP(D18,s,SEQUENCE(1,10,0),-999, 0 ) returns the exact match

Play with that a little

I played around with the 3 options, the first two as you mention return as stated, the third option with the zero, returns a -999, that had me confused. So in using the excel formula builder which I don't have a lot of experience with, I came up with a multiple cell scenario that seems to work so I'll work with what I have here.
Thank you for taking interest.
Doug.

 

[Jeffrey Mahoney]

Between the suggestions from my last post and the Sequence part of the formula I know you'll make it work. The Sequence function can start at zero or any other number