MAX & MIN 'n' stuff 1

[jonsi]

I have a sheet that contains details of issues of fuel to various vehicles. Every time fuel is issued, a mileage reading is entered for the vehicles. I can summate the issues/costs etc. by vehicle for a given time period using a SUMPRODUCT string against WEEKNUM based on the date of the fuel issue.

What I'd like to do is calculate the mileage per vehicle over the same time period. I don't want to sort the data table as it relies on formula driven meter readings to give other data for the fuel tank stocks / aggregate values etc.

In essence I'm looking for a kind of MINIF & MAXIF type of function so that I can calculate the data I need without the need for a massive sting of IF(AND) functions.

Any ideas?

 

[GlennUK]

Something like

=MAX(IF(vehRange=vehicle,mileRange,0))-MIN(IF(vehRange=vehicle,mileRange,0))

entered using Ctrl-Shift-Enter instead of Enter.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jonsi]

Thanks Glenn ...

Using what you've suggested I can get the MAX mileage over the range of entries vehicle by vehicle (MIN is playing silly Debuggers though) but it isn't exactly what I need.

I think what I'm after is along the lines of a =MAX(IF(AND formula bringing the WEEKNUM into play as well . I've tried it a few different ways using IF(AND but I just get Zero (the else bit of the string) returned as the result.

 

[GlennUK]

Yes, MIN will be playing silly debuggers .... I typed ,0 inside the MIN thing instead of 9999999.

So you want this for a particular week number? Do you have the week number already calculated in the table?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jonsi]

Each issue of fuel (for whichever vehicle) is accompanied by a date keyed in. Using =WEEKNUM(datecell,1) gives the Week Number - I use this for a SUMPRODUCT function that lists the SUM of fuel used by individual Vehicles for the given week in a table.
=SUMPRODUCT((VEHICLE_RANGE=VEHICLE)*(WEEK_RANGE=WEEK)*(FUEL_RANGE))

some conversions are necessary to make sure that Text isn;t included in a numeric calculation but these are easily overcome

 

[GlennUK]

Try this:

=MAX(IF(vehRange=VEHICLE,IF(weekRange=WEEK,mileRange,0),0))-MIN(IF(vehRange=VEHICLE,IF(weekRange=WEEK,mileRange,999999),999999))

entered using Ctrl-Shift-Enter instead of Enter.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jonsi]

Glenn ...behold ...A STAR!

Thanks a lot - works like a charm!

 

[GlennUK]

Aha, I love them stars.

Once you get the idea behind these array IF formulae, they can do a great deal of useful stuff for you. Glad it was helpful to you

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.