Find and report the first value over 6000 in a row

**lctwigg** · 01-04-2016, 10:49 AM

Hi All,

New to the forums so apologies if this has been asked before - hopefully I can ask in a coherent way also!

I have a sales sheet with months in the headers and accounts in the rows with the amount spent in the month in the middle.

I want to find the first spend over £6000 for each account and ideally return the month that this spend happened to populate row G as per the below example:

A B C D E F G
1 Customer Jan 15 Feb 15 March 15 April 15 May 15 Sales target hit
2 Company A £1254 £6001 £7589 £7854 £6524 Feb 15
3 Company B £3254 £2452 £4251 £4587 £6854 May 15
4 Company C £5654 £5874 £5432 £6432 £6584 April 15
5 Company D £3254 £3524 £2547 £4584 £5215 N/A

I have tried to do Match and Index to no avail. Initially i started by trying to return the first result above £6000 and then in the next cell doing a look up to the header however it keeps finding the last cell instead.

This is the formula I've tried:

=INDEX(BG96:CD96,MATCH(TRUE,INDEX(BG96:CD96>6000,0)))

the array is the specific row im looking at however it seems to return a random number in the row and not the first. I then used another index match to return the header as couldn't work out how to find the first greater than £6000 number and return the header in one formula.

Any help would be greatly appreciated,

Many Thanks,

Laura

**XOR LX** · 01-04-2016, 11:01 AM

Hi.

Use an exact match_type parameter:

=INDEX(BG96:CD96,MATCH(TRUE,INDEX(BG96:CD96>6000,0),0))

Regards

**lctwigg** · 01-04-2016, 11:15 AM

Thanks for your reply however this hasn't worked I'm afraid.

I have attached an example of my spread sheet to better communicate my problem. As you can see there are many occasions where it is over £6000 but not returned. The forumla im looking for is in columns Z and AA

Thanks in advance,

Laura

**XOR LX** · 01-04-2016, 11:19 AM

Can you give me an example of a row in here where there is a value greater than £6,000 yet no value is returned by the formula?

Regards

**lctwigg** · 01-04-2016, 11:22 AM

Yes, I used a conditional format on the attachment to show all values over £6000. Only one is returned at the moment.

**XOR LX** · 01-04-2016, 11:23 AM

Ah. I see. Blanks are being considered here as ">6000".

Try:

=INDEX(BG96:CD96,MATCH(TRUE,INDEX(0+BG96:CD96>6000,0)

Regards

**lctwigg** · 01-04-2016, 11:27 AM

that returns the last cells with a value in it for some reason. Is it because my array is a row and not a list?

**AlKey** · 01-04-2016, 11:29 AM

Try this one

Fr the example provided on your spreadsheet.

Formula:

Please Login or Register  to view this content.

**lctwigg** · 01-04-2016, 11:41 AM

This works for the first row but when i drag it down it seems to bring hte wrong result for some reason. Is it hte way im dragging it down?

I have re- attached the spread sheet with this formula in there,

Thanks again for all your help!

**XOR LX** · 01-04-2016, 11:41 AM

Originally Posted by lctwigg

that returns the last cells with a value in it for some reason. Is it because my array is a row and not a list?

Can I see an example in a workbook of this?

Regards

**lctwigg** · 01-04-2016, 11:45 AM

Hi XOR LX,

I have copied your previous formula into the attached spread sheet.

Again, appreciate your help

**AlKey** · 01-04-2016, 11:50 AM

Originally Posted by lctwigg

This works for the first row but when i drag it down it seems to bring hte wrong result for some reason. Is it hte way im dragging it down?

I have re- attached the spread sheet with this formula in there,

Thanks again for all your help!

No sure what exactly you talking about. All results show the first value that is greater than 6000. Or maybe you expect all values that are greater than 6000 appear in one cell?

**lctwigg** · 01-04-2016, 11:56 AM

Sorry it doesn't, Z5 returns 6716(J5) when it should return 7967(G5) and Z20 should return 7980 (J20) but returns N20 6761. It looks to me that its returning the smallest value over £6000 not the first in the row.

**XOR LX** · 01-04-2016, 12:05 PM

Your original post in this thread:

Originally Posted by lctwigg

I want to find the first spend over £6000 for each account

**XOR LX** · 01-04-2016, 12:07 PM

Originally Posted by XOR LX

Ah. I see. Blanks are being considered here as ">6000".

Try:

=INDEX(BG96:CD96,MATCH(TRUE,INDEX(0+BG96:CD96>6000,0)

Regards

Sincere apologies. Having a bad day. Now I've missed off the additional zero that I previously advised you about:

=INDEX(B2:Y2,MATCH(TRUE,INDEX(0+B2:Y2>6000,0),0))

Regards

**lctwigg** · 01-04-2016, 12:12 PM

This works a dream, thank you

**newdoverman** · 01-04-2016, 01:23 PM

The blanks in the worksheet are not blanks but contain something that doesn't return a code and is invisible. After deleting the contents of the "blank" cells, this works

Formula:

Please Login or Register  to view this content.

There are 7565 of those mystery cell contents.
You can get rid of the mystery contents by selecting B2 and copy. Hit escape. Go to Find and Select, Replace, click in the find what and Ctrl + V (shortcut for paste) enter something like ZZ in the Replace With field. Then click replace all. Now in the Find What field enter ZZ and in the Replace with field delete all contents and replace all.

**XOR LX** · 01-05-2016, 05:48 AM

@Ron

Perhaps, but that formula will fail if those "blanks" are the not-so-mysterious, good old null string ("") as well, since Excel considers:

="">6000

to be TRUE.

Regards

**newdoverman** · 01-05-2016, 10:03 AM

@XOR LX those could be "" as they don't register when using CODE to get what is in the cell or even if one used in =CELL X= CHAR(34) to get a TRUE/FALSE return. There weren't any formulae in those cells and if you just type "" into a cell they are visible and will return a value 34 for CODE. The values must be a "dump" from somewhere else if the contents are indeed "" as a copy of a cell with a null value and past values of that copy will produce invisible results without a formula being present.

Whatever it is, I don't think that it should be allowed to continue in a worksheet that is in use because of the potential problems (as we found out with the multiple failed attempts at finding the correct values) that were caused.

**XOR LX** · 01-06-2016, 04:50 AM

Totally agreed with you that this should be rectified: nobody should leave such characters lurking within a spreadsheet.

I was merely wishing to emphasize to potential readers of this thread the fact that the more innocent and common character that is the null string will also cause problems to "standard" solutions here which involve a comparison to numeric values (e.g. >6000).

Regards

Find and report the first value over 6000 in a row

LinkBack

Thread Tools

Rate This Thread

Display

Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Re: Find and report the first value over 6000 in a row

Thread Information

Users Browsing this Thread

Similar Threads

Agging report, openingbalance report,customer report vab code was not working

Agging report, openingbalance report,customer report vab code was not working

[SOLVED] Subtracting all data for just say 30 entries from say 6000 entries

Two spreadsheets, one is 6000 long the other 800. I need to find the 800 in the 6000

Delete all rows with formulas from row 6000 and lower

Find highest value and report a different name

[SOLVED] Can't find Report Manager in XP

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions