error in 1 line of INDEX code :-(

[John Caines]

Hello All.
I have 1 line of code which is;

=IF(LEN($O$24)=0,"",IF((INDEX($AI$24:$AI$274,MATCH(9000000000+307,$AI$24:$AI$274)))-INDEX($AG$24:$AG$274,COUNT($AI$24:$AI$274))>0,"YES","NO"))

At the moment cell AI45 shows an amount of £12,662.00 (This Is ACTUAL AMOUNT)
Cell AG45 Shows an amount of £12,702.58 (This is TARGET AMOUNT)

So,, the answer should show NO,, as the actual amount isn't greater than the target amount,,,,but it shows YES!
Because the formula is quite complicated,, I'm not sure what is wrong,,,
What AG's to change to AI's etc.

If someone could please show me what is wrong with the above formula,, As I'm just not sure what to change,,, even if I do change something and get a YES,, I wouldn't be confident, or 100% sure it would then be correct

I hope all the above makes sense.
Many Thanks for all your time
Best Regards
John Caines

 

[John Caines]

I've managed to redo a formula from Aladin Akyurek,,,

=IF(LOOKUP(9.99999999999999E+307,$AI$24:$AI$274) > 
     LOOKUP(9.99999999999999E+307,$AG$24:$AG$274),"YES","NO")

This seems to work,,, I think I'll go with this,,, but another question,,,
If the 2 £ amounts were even,,, then I would like the formula to show "YES" also,,, at the moment,,, with the above formula it shows "NO"

Any way around this???
Not sure how to write this...

Many thanks
John Caines

 

[John Caines]

would it be;

    =IF(LOOKUP(9.99999999999999E+307,$AI$24:$AI$274) >= LOOKUP(9.99999999999999E+307,$AG$24:$AG$274),"YES","NO")

????????
Seems to work,, so now with the above formula,,,
If AI is greater than AG it shows YES

If AI is Less than AG it shows NO

If AI is the same as AG it shows YES

If someone can just confirm the above is written correctly please.
I think it is,,, but not sure if it's the best way.

Many Thanks
John Caines

 

[T. Valko]

I've managed to redo a formula from Aladin Akyurek,,,

=IF(LOOKUP(9.99999999999999E+307,$AI$24:$AI$274) > 
     LOOKUP(9.99999999999999E+307,$AG$24:$AG$274),"YES","NO")

This seems to work,,, I think I'll go with this,,, but another question,,,
If the 2 £ amounts were even,,, then I would like the formula to show "YES" also,,, at the moment,,, with the above formula it shows "NO"

Any way around this???
Not sure how to write this...

Many thanks
John Caines

Try it like this...

=IF(LOOKUP(1E100,$AI$24:$AI$274)>=LOOKUP(1E100,$AG$24:$AG$274),"YES","NO")

 

[John Caines]

Many Thanks T Valko,,, seems to work great!!
So,, basically 9.99999999999999E+307, is now replaced by the shorter 1E+100
???
Shorter on the code,, great stuff Valko.

I might as well mention this,, seeing as you have shown me a new way to implement the code,,,In my same sheet I have 2 other lines of code,,
being;

=IF(LEN($O$24)=0,"",LOOKUP(9.9999999999999E+307,$AI$24:$AI$274))

&

=IF(LEN($O$24)=0,"",OFFSET(INDEX($AI$24:$AI$274,MATCH(9000000000+307,$AI$24:$AI$274)),,-2,,))
[code]

I'm no coder Valko,,, so maybe the above 2 can be written better,,, but with your 1E+100 idea,,,
would these now be
[code]
=IF(LEN($O$24)=0,"",LOOKUP(1E+100,$AI$24:$AI$274))

&

=IF(LEN($O$24)=0,"",OFFSET(INDEX($AI$24:$AI$274,MATCH(1E+100,$AI$24:$AI$274)),,-2,,))

???
They seem to work ok,,, if there is any chance you could just confirm these are altered correctly Valko,, that would be a great help. (Not sure what the +100 does?)

Many many thanks for your reply Valko,,,
greatly appreciated.
Best regards...
A very grateful
John Caines

As a note;;;

=IF(LOOKUP(1E+100,$AI$24:$AI$274)>=LOOKUP(1E+100,$AG$24:$AG$274),"YES","NO")

I entered it originally with the + in,, so 1E100 was 1E+100,,,,,,,,not sure which is the correct way Valko??

 

[John Caines]

Just found the answer to the 1E100 & 1E+100 Valko,,,

"1E100 and 1E+100 mean the same thing in Excel. It's scientific notation > for a very large number, 1 followed by 100 zeros",,, found on google,,,,
So,,, no I know

 

[T. Valko]

Many Thanks T Valko,,, seems to work great!!
So,, basically 9.99999999999999E+307, is now replaced by the shorter 1E+100
???
Shorter on the code,, great stuff Valko.

I might as well mention this,, seeing as you have shown me a new way to implement the code,,,In my same sheet I have 2 other lines of code,,
being;

=IF(LEN($O$24)=0,"",LOOKUP(9.9999999999999E+307,$AI$24:$AI$274))

&

=IF(LEN($O$24)=0,"",OFFSET(INDEX($AI$24:$AI$274,MATCH(9000000000+307,$AI$24:$AI$274)),,-2,,))
[code]
 
I'm no coder Valko,,, so maybe the above 2 can be written better,,, but with your 1E+100 idea,,,
would these now be
[code]
=IF(LEN($O$24)=0,"",LOOKUP(1E+100,$AI$24:$AI$274))

&

=IF(LEN($O$24)=0,"",OFFSET(INDEX($AI$24:$AI$274,MATCH(1E+100,$AI$24:$AI$274)),,-2,,))

???
They seem to work ok,,, if there is any chance you could just confirm these are altered correctly Valko,, that would be a great help. (Not sure what the +100 does?)

Many many thanks for your reply Valko,,,
greatly appreciated.
Best regards...
A very grateful
John Caines

As a note;;;

=IF(LOOKUP(1E+100,$AI$24:$AI$274)>=LOOKUP(1E+100,$AG$24:$AG$274),"YES","NO")

I entered it originally with the + in,, so 1E100 was 1E+100,,,,,,,,not sure which is the correct way Valko??

Yes, they are correct.

1E100 (or, after Excel "tweaks" it to 1E+100) is scientific notation for a very very very large number just like 9.9999999999999E+307 is scientific notation for a very very very large number.

Both will work but I prefer the much simpler to remember and less confusing 1E100. I don't have to count how many 9s when using 1E100.

In a nutshell, you need a lookup value that is greater than any numeric value in the range.

I like to use the bowling example to describe this.

Let's say you're doing some work for your bowling league. You know that the maximum possible score for a round of bowling is 300.

So you want to find the last score in a column.

Using the LOOKUP function, here are some options:

=LOOKUP(301,A:A)
=LOOKUP(1E100,A:A)
=LOOKUP(9.9999999999999E+307,A:A)

Each one of those formulas will return the correct result.

Agian, the lookup value just has to be greater than any numeric value in the range. It's that simple although some would have you believe otherwise.

 

[John Caines]

Many thanks for your reply Valko,,, great stuff....
Yes,,,, it is a lot simpler with the E100 code,,,,,
I shall implement this now..great idea

Many thanks again for this info,,,
Spreadsheet is almost done,,,
just got to sort out a import column macro,, but that's another post for sure

many thanks again Valko

Best regards
John Caines

 

[Aladin Akyurek]

Many thanks for your reply Valko,,, great stuff....
Yes,,,, it is a lot simpler with the E100 code,,,,,
I shall implement this now..great idea

...

I don't see what is great about replacing or insisting on replacing a constant that Excel knows...

If one has any difficulty with 9.99999999999999E+307, one can just define

BigNum

as referring to:

=9.99999999999999E+307

If you don't want this move, use a shortened version like:

9.99E+307

like done in maths with

<DL><DD>π = 3.14159265358979323846264338327950288419716939937510...</DD></DL>which often becomes:

3.14

What is important is the notion that LOOKUP(BigNum,Reference) and kindred expressions,
not zillions of variants on BigNum one comes across.

 

[T. Valko]

Many thanks for your reply Valko,,, great stuff....
Yes,,,, it is a lot simpler with the E100 code,,,,,
I shall implement this now..great idea

Many thanks again for this info,,,
Spreadsheet is almost done,,,
just got to sort out a import column macro,, but that's another post for sure

many thanks again Valko

Best regards
John Caines

You're welcome. Thanks for the feedback!