I am trying the following formula and my return is 0-Jan-00
=MAX(IF($G$2:$G$6239=$L7,$B$2:$B$6239,0))
Col G contains all vehicle numbers, Col L is the vehicle #, Col B contains all fuel transaction dates for that vehicle.
I am trying to find the MIN & MAX dates to do a VLOOKUP for the odometer reading in Col E for each MAX & MIN date to get total mileage for that month. My return is 0-Jan-00. The file is quite large, over 7,000 lines of data, I can send a smaller example if needed. Thanks.
Hi,
The first thing to check is that you've entered that formula as an array formula, which I can tell it must be in order to give you the correct result.
Regards
Oops, forgot to type those in, that's my return with the array formula
maxifminifexample.xlsx
Hope I've attached the sample file correctly.
Col L thru P is where I'm building my formulas, M & N are max min dates, Col O & P will Vlookup the Odometer for the corresponding date and veh #
Then I can only imagine that there are no entries in the range G2:G6239 which are identical to that in L7.
Have you manually checked this (with e.g. copying the entry in L7 and using the in-built Find feature)?
Regards
You say: "Col G contains all vehicle numbers", though in this sheet that column is labelled ACCOUNT #?
Regards
So it is, darn. Too easy, now I tackle the Vlookup end of it, thanks.
these are the accounts in col g
ACCOUNT #
4
1
7
13
73
10
6
=MAX(IF($G$2:$G$6239=$L5,$B$2:$B$6239,0))
l5 =2 so the answer is 0
its a quirk of excel that 0 formatted as date is
January 0, 1900 which obviously is not a real date value
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
In my example, I've copied M7 to N7 changing the MAX to MIN, I get a return of 2 Sep 13.
The Vlookup in O8 matches the odometer in E for the MAX date but the odometer for the MIN date in N7 returns for the odometer for vehicle 13 in cell E85.
What am i missing, I need the MAX & MIN dates for only vehicle 8.
Each MIN & MAX formula seems to find the first MAX & MIN date in Col B vs. only the vehicle # in L7.
OOPS, change vlookup O8 below to O7
Originally Posted by MARKSTRO
careful with min(if it tends to see blanks as 0 and returns 0 as minimum min(if needs to be something like
=MIN(IF((A1:A100=A1)*(B1:B100>0),B1:B100)) array entered
I think the problem is how to narrow the MIN MAX date return to only those that match the vehicle number in col L, currently I'm getting just the MAX MIN date for the whole range B:B.
These worked for me (the first ones in under M2 and N2 are Array Formulas (Cntrl+Shift+ Enter, not just Enter), while the options are just normally entered)
In M2:
Array Formula (CSE):
Formula:
Please Login or Register to view this content.
Option (regular):
Formula:Please Login or Register to view this content.
In N2:
Array Formula (CSE):
Formula:Please Login or Register to view this content.
Option (regular):
Formula:Please Login or Register to view this content.
In O2 (I'assuming you want the last Odometer reading in this column, and starting in the next):
Formula:Please Login or Register to view this content.
In P2:
Formula:Please Login or Register to view this content.
Drag select the 4 cells, then extend down as far as needed
While all the cells are selected (hilighted), go to conditional formatting,hilight cell rules, equal to, put 0 in the entry box and select cusom for the format,number,custom - ;;"" in the Type box, OK,OK
Hope this helps
(Note the options are still array formulas, the Sumproduct wrapped around them just avoids having to CSE enter them )
Last edited by dredwolf; 11-06-2013 at 04:37 PM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
I've attached my latest, row 7 is where I've been working from as it's the first vehicle in F:F.
M7:Q7 appears what I'm after, when I copy O & P down I get the False in the IF statement????
These worked for me (the first ones in under M2 and N2 are Array Formulas (Cntrl+Shift+ Enter, not just Enter), while the options are just normally entered)
In M2:
Array Formula (CSE):
Formula:Please Login or Register to view this content.
Option (regular):
Formula:Please Login or Register to view this content.
In N2:
Array Formula (CSE):
Formula:Please Login or Register to view this content.
Option (regular):
Formula:Please Login or Register to view this content.
In O2 (I'assuming you want the last Odometer reading in this column, and starting in the next):
Formula:Please Login or Register to view this content.
In P2:
Formula:Please Login or Register to view this content.
Drag select the 4 cells, then extend down as far as needed
While all the cells are selected (hilighted), go to conditional formatting,hilight cell rules, equal to, put 0 in the entry box and select cusom for the format,number,custom - ;;"" in the Type box, OK,OK
Hope this helps
(Note the options are still array formulas, the Sumproduct wrapped around them just avoids having to CSE enter them )
As you are not using the formulas I suggested (you can start them anywhere, just adjust the ranges accordingly), I'm not sure what you want..the false values are appearing, becuase those formulas should be array formulas, but even if they were, I believe you are goint to get a lot of #value errors as well
I've uploaded what I worked out, with the options in the columns after the Array formulas, you can delete the forulas in M2 through Q6 with no harm (see attached,where I have already done this)
Hope this helps
I did try your formulas, forgot to mention that, they did not work that's why I sent you the closest to the solution I had.
I've checked your submission out, it works, still trying to fathom the formula logic.
I guess Col m:p and q:t are different formulas with the same results, there are so many ways to get the same results I get confused.
Thanks so much, looks like it will work
maxifminifexampleVERSION3.xlsx
I've attached version 3 of my first example, copying your formulas in M7 and copying down arrays and regular formulas and the returns repeat.
What am I doing wrong, the formulas look like they should work.
Well when I downloaded the sample, It seems to be working, yif the repeated dates are a concern(not sure why that would be, but...) thats what the sample has, several end dates are the same, the question is are the odometer readings related to those dates and cars right, and as far as I can see, they are...
I dragged the formulas down a little further, and they are still returning the right values, so I'm not really sure what the problem is?
you have multiple same dates, because they are the MIN/MAX dates on those cars, and they are the same, try changing some of the date values for the earliest, latest date for a particular car # and the formulas reflect it, change the odometer readings for either the first or last date, and the formulas reflect it...maybe I am missing something?
since dates are ascending and you want to check the start and end odometer of a particular vehicle
start=
=INDEX($E$2:$E$300,MATCH(M4,$F$2:$F$300,0))
end=
=LOOKUP(2,1/($F$2:$F$300=M4),$E$2:$E$300)
those give
for vehicle 8
131357 133048
for vehicle 9
90738 92055
vehicle 10
80436 82101
Last edited by martindwilson; 11-06-2013 at 09:43 PM.
OK, thanks for all the help, dredwolf, your formulas you gave for col Q:T work just fine now.
One last question, when the vehicle fuels twice on the same day, the mileage is added together, how can I get a return for the either the MIN/MAX when I have two entries for the same date?
martindwilson, your suggestion returns just one of the mileage entries, but the first one in the string, how can I designate the Max entry of identical date entries?
in your sample all in sept ,all the dates are ascending for each vehicle so the formulas return first and last entries for a given vehicle it doesnt look at dates
Last edited by martindwilson; 11-07-2013 at 03:57 PM.
Got it Martin, thanks. All is good with my worksheet now.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks