Hi
I've got a big predicament.
From the table attached, I need a formula that will look through only the money amounts and return the first amount found.
I tried INDEX & MATCH formula =INDEX(B40:F40,MATCH(1,IF(B40:F40<>0,IF(B40:F40<>"",1),0))). However, I am unable to edit the formula to look through all the range without taking into account the heading of the table.
Any help? Thanks in advance.
2012-07-31_150858.png
If you're willing to "fix" your table so that it is a standard two-row table with Date references in row1 and lookup data in row2, I can show you a formula to find the first value in THAT table. If you're going to keep that unfriendly data layout, I wouldn't spend time on that.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Unfortunately by changing the layout I'd compromise other parts of the spreadsheet layout. So, I need to find a way out. Is there any way to combine multiple arrays into one? Is it possible to repeat the formula for the first row for the second and third row in loop? In case there's nothing on first, it goes onto the second and eventually onto the third row if nothing is found on the second.Originally Posted by JBeaucaire
This array formula will check A2:D2 for the first numeric value:
=INDEX($A$2:$D$2, SMALL(IF($A$2:$D$2<>"", COLUMN($A$2:$D$2), ""), 1))
This is confirmed by pressing CTRL-SHIFT-ENTER to activate the array, you will see braces { } appear around your formula. You can create versions of that formula for each row and nest them together.
Something like:
=IF(COUNT($A$2:$D$2)>0, INDEX($A$2:$D$2, SMALL(IF($A$2:$D$2<>"", COLUMN($A$2:$D$2), ""), 1)), IF(COUNT($A$4:$D$4)>0, INDEX($A$4:$D$4, SMALL(IF($A$4:$D$4<>"", COLUMN($A$4:$D$4), ""), 1)), IF(COUNT($A$6:$D$6)>0, INDEX($A$6:$D$6, SMALL(IF($A$6:$D$6<>"", COLUMN($A$6:$D$6), ""), 1)), "none")))
...confirmed with CSE.
Last edited by JBeaucaire; 07-31-2012 at 02:30 PM.
In my case it was to work, however it doesn't seem to be working properly.
First of all, the cells in that range are "blank" not empty, that is, they contain formulas to return "" in case they don't find specific parameter in the cells they are pointing to.
Secondly, after deleting the content of those cells as a experiment, the formula "worked" of sorts, that is, it returned not the very first numeric value found along the way but the bigger numeric value. In a way that, if the first was smaller than the second, it returned the second one.
Last edited by Cutter; 08-01-2012 at 01:15 PM. Reason: Removed whole post quote
Array confirmed,
Could get a bit messy if your real table is bigger than your sample image.PHP Code:=INDEX(INDEX((B40:F40,B42:F42,B44:F44),,,MIN(IF(CHOOSE({1;2;3},B40:F40,B42:F42,B44:F44)<>"",{1;2;3}))),MATCH(TRUE,INDEX((B40:F40,B42:F42,B44:F44),,,MIN(IF(CHOOSE({1;2;3},B40:F40,B42:F42,B44:F44)<>"",{1;2;3})))<>"",0))
Show me the file so I can see your edited version of the formula. It works for me giving the first value found in the first row, not the lowest.
Here it is
=IF(COUNT($B$40:$F$40)>=0,INDEX($B$40:$F$40,SMALL(IF($B$40:$F$40<>"",COLUMN($B$40:$F$40),""),1)),IF(COUNT($B$42:$F$42)>1,INDEX($B$42:$F$42,SMALL(IF($B$42:$F$42<>"",COLUMN($B$42:$F$42),""),1)),IF(COUNT($B$44:$C$44)>1,INDEX($B$44:$C$44,SMALL(IF($B$44:$C$44<>"",COLUMN($B$44:$C$44),""),1)),"")))
2012-08-01_150426.png
THat's not a workbook. That's a picture. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. I need to see the formula failing.
Apologies.
Did you try the alternative formula I provided in reply #6?
This would be the array formula:
=IF(COUNTIF($B$40:$F$40, 0)<5,INDEX($B$40:$F$40,SMALL(IF($B$40:$F$40<>"",COLUMN($B$40:$F$40)-1,""),1)),
IF(COUNTIF($B$42:$F$42, 0)<5, INDEX($B$42:$F$42,SMALL(IF($B$42:$F$42<>"",COLUMN($B$42:$F$42)-1,""),1)),
IF(COUNTIF($B$44:$C$44, 0)<2,INDEX($B$44:$C$44,SMALL(IF($B$44:$C$44<>"",COLUMN($B$44:$C$44)-1,""),1)),"")))
Well, sorry, but the formula displayed no result, not even an error.
---------- Post added at 09:33 AM ---------- Previous post was at 09:29 AM ----------
Yes, I did. It displayed no result.
Here's your workbook back with the formula as shown, it is displaying the answer:
If you change the date to the beginning of the year, say, 01/03/2012 to 31/12/2012, the formula fails.
I'm so sorry, but I was thinking if the insertion of the function ISERROR along the formula would make the formula work in a loop and catch any instance or row where the first non blank cells took place.
What to you think?
Last edited by JBeaucaire; 08-01-2012 at 05:53 PM. Reason: removed inappopriate emotional references that are not relevant to the discussion.
I think the formulas ABOVE this section don't do waht they appear to do. They look like they are supposed to put NULL values in cells, but they aren't, they are putting zeros in there and your display is suppressing them.
I reentered the same formula, this time as an array and it seems to fix the problem. Click on F49, press F2, then CTRL-SHIFT-ENTER.
Like i suggested earlier, there's no reason to work this hard. I recreated the Month tables off to the right in a simple linear format, then set links in your main table to just display the calculated values from there, that makes the formula we're working remarkably simple in comparison.
Array-entered:
=INDEX($R$33:$AC$33, MIN(IF(R33:AC33>0,COLUMN(R33:AC33)-17)))
The PRINT AREA of the sheet has been adjusted to ignore that outer area.
As Jerry has already pointed out, your other formula are returning 0 not blank cells, which fouls up the logical tests, with those you would need to test for 0 not blank.
In addition, you have " " in parts of your formula where you want blanks returned, although this appears blank and wouldn't be evaluated as 0 in formula, it could cause similar problems if you ask for a formula to give results based on blank cells. Common practice is to use "" for blank, not " ", when compared in a formula they evalaute as different.PHP Code:=INDEX(INDEX((B40:F40,B42:F42,B44:F44),,,MIN(IF(CHOOSE({1;2;3},B40:F40,B42:F42,B44:F44)<>0,{1;2;3}))),MATCH(TRUE,INDEX((B40:F40,B42:F42,B44:F44),,,MIN(IF(CHOOSE({1;2;3},B40:F40,B42:F42,B44:F44)<>0,{1;2;3})))<>0,0))
It works great indeed. Thank you very much.
On the other hand, I understand the INDEX function. However, could you please breakdown the MIN function form me? If I understood well, R33:AC33>0 is the first argument. But what is COLUMN(R33:AC33) and especially -17?
Thanks.
You want the FIRST value that appears in the table.
COLUMN() is a function that returns column numbers.
MIN() is a function that returns the minimum value in an array of values.
The formula given tests the entire range R33:AC33 and creates a TRUE/FALSE array for the cells that are > 0.
For the ones that are TRUE, the COLUMN number is added to the array. We need to adjust all those numbers down by 17 because our values start in column 18 and we want the lowest possible value to be 1, not 18.
From the array of columns that have values, the MIN function extracts the lowest column number and feeds it back to the INDEX.
You can click on the cell with that formula and use the Evaluate Formula function on the Formula Auditing toolbar, watch it unfold one calculation at at time, very informative.
If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
Fantastic! Thank you a million!!!
Last edited by Cutter; 08-02-2012 at 05:15 PM. Reason: Removed whole post quote
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks