yes or no by a condition formula

[John Caines]

Hello All.
I have a range:
W24:W274.
after each day a £ amount is entered, so at present (After 3 weeks) it has 15 entries in (5 per week-Mon -Fri).
In the column next to it, Actually it's Column U merged across with Column V, there are some other £ amounts.

What I'm wanting to do is this;
If the LAST entered Cell in Column W is greater than the amount in the column to its left (Column U merged with V), I want to display the word YES In cell Q9.
If The Last Entered Cell In Column W is LESS than the amount in column to the left of it ,, cell Q9 will display the word "NO"....

Simple?
Can I do it, no,,,
Trying to find it on forum,, I'm sure its a common 1.
If someone can point me in the right direction please.

Many Thanks
john Caines

 

[John Caines]

Just to add;
Column U24:U274 is complete, has data (£'s ) in each cell.

The column W,, is entered in daily,, so the formula has to ask,, whatever the last value in column W (Range W24:W274) is,, run the formula to see if this £ amount is greater than the amount to the left (Column U),,

Hope this makes sense.

Many Thanks to you all
John Caines

 

[John Caines]

This is tricky,,,,
Is it needing a "OFFSET" in the formula?
Not sure,,,,,

Been trying to search for it,, but a hard 1 to find.

Help

 

[Dryver14]

do the 2 numbers run parallel which need comparing?

 

[dimitri]

Assuming that the cells in column W are empty before you enter the £ value, this formula should work.

=IF((INDEX(W24:W274,MATCH(9.9E+307,W24:W274)))-INDEX(U24:U274,COUNT(W24:W36))>0,"YES","NO")

 

[John Caines]

Hi Dryver14,,
Answer to your question is yes,, they run parallel..

dimitri,,,,,,
I've just tried your formula,, Working !!!

Wow,,, It looks more complicated than I thought,,,,
excellent Job dimitri,,, I could never have gotten that in 1 million years.

Many many thanks for this,,,,
1 more question though,,, as it is for the same sheet,,, and is a very similar question,,, so hopefully a similar type of formula.

Question is;
The actual balance that shows in column W24:W274 is added daily.
What would the formula be to show the cell £ Amount to the left (Which is Column U) that is OPPOSITE the last entered amount in Column W???

Example.
The last Amount entered in Column W is actually Cell W39 (Which shows an amount of £200)
I need to return the £ amount In Cell U39 (Which is OPPOSITE W 39) to a nominated cell Q9.

Is this a similar formula?
If you could solve this 1 for me, that would be fantastic.
Again, many many thanks for this.
All the best
A Very Happy & Grateful
John Caines

 

[Dryver14]

Try this in Q9


=OFFSET(INDEX(W24:W274,MATCH(9000000000+307,W24:W274)),,-2,,)

 

[Aladin Akyurek]

Q9:

=IF(LOOKUP(9.99999999999999E+307,W24:W274) > 
     LOOKUP(9.99999999999999E+307,U24:U274),"YES","NO")

You can even replace these definite ranges with: W:W and U:U, respectively, if theses columns are not used other purposes.

 

[Aladin Akyurek]

This is tricky,,,,
Is it needing a "OFFSET" in the formula?
Not sure,,,,,

Been trying to search for it,, but a hard 1 to find.

Help

No, it doesn't...

 

[John Caines]

Great stuff Dryver14,,, !! Working.

It's 4.30am here,, just had to look before I go to work (God how I hate early morning Saturday 12 hr shifts ,,,)

Excellent.
Many thanks for this,, not many cells to go now,,
I think I can fill the others in, I'll try when I'm back from work.

As to your formula Aladin Akyurek,
It returned a No or a Yes,, but for the Q9 cell it needed to return the actual amount opposite the cell in column W (So if the last filled cell in w was W35, I needed to show the £ amount in Q9 from cell U35).

Many thanks for your input also, all greatly appreciated.

All the best
A very grateful
John Caines