Is there a function that returns the first value in an array?

**daljaxon** · 01-24-2007, 02:53 PM

Hi,

I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.

In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.

Thanks,
Daljaxon

**MrShorty** · 01-24-2007, 02:56 PM

Is the INDEX function what you are looking for?

pinmaster · 01-25-2007, 10:07 AM

Hi

It would help if people could see a sample of your data or know how your spreadsheet is setup.

Regards!
Jean-Guy

**daljaxon** · 01-25-2007, 02:54 PM

What would be the best way in displaying the work sheet?
Can I put the spread sheet on a server were people can view it?
Or can I upload it here somehow?

Daljaxon

**daljaxon** · 01-25-2007, 06:28 PM

Hi MrShorty,

I don't think the index function would work. What I read up on that function is it doesn't pick from a random set of numbers and it picks up on a designated spot in the formula.

What I am looking for, if Excel does this, is a function that will look at all the values of a group of cells and return the first data value so I can subtract it from the overall sum of all the values. I am sure there isn't a sum function that ignores the first value in a group of cells. Of course all the values will be entered in random order since I am inputing the data on the dates of that the vehicles are fueled up.

I can email an example excel sheet to anyone who is interested.

Let's say that I have a group of cells, I4:I34, these cells are labeled Gallons. These cells are in line with the days of the month on one specific truck. On different days the employee gets fuel. I write down the amount of fuel, 10 gals, 14 gals, etc... Of course this is recorded randomly depending when they need fuel.

Now I need to sum all the gallons up. That is easy, "=Sum(I4:I34)," in the total cell. Ok, now I want to subtract the first recorded gallon amount, because when you subtract the mileage of the first entry from the last entry, the first recorded mileage amount is not needed and would give you a false reading of the MPG. Now I can go into each month and subtract that first entries cell value, but I wanted to have a preset function that will do this automatically to save time.

So I am trying to find a function that like the count function, will see all the nonblank cells and ignore the empty cells and be able to return the first value of the random entered values.

Thanks,
Daljaxon

pinmaster · 01-25-2007, 07:21 PM

Hi,

Try something like this:

assuming you have your dates in column A, A1:A365 for all the days of the year, B1:B365 is the gallon column.

So if we need to sum only the gallons from a specific month we would use:

=SUMPRODUCT(--(MONTH(A1:A365)=1),B1:B365)

and to get the first gallon value of the month we could use something like:

=INDEX(B1:B365,MATCH(MIN(IF((MONTH(A1:A365)=1)*(B1:B365>0),A1:A365)),A1:A365))
this is an array formula so needs to be entered using Ctrl+Shift+Enter

or we could use the two together like this:

=SUMPRODUCT(--(MONTH(A1:A365)=1),B1:B365)-INDEX(B1:B365,MATCH(MIN(IF((MONTH(A1:A365)=1)*(B1:B365>0),A1:A365)),A1:A365))

again entered using Ctrl+Shift+Enter

Hope this helps!
Jean-Guy

**MrShorty** · 01-25-2007, 07:25 PM

I don't think there is a single built in function to do what you want. Here's how I did it, and it does involve the INDEX function. =INDEX(I4:I34,MATCH(false,isblank(I4:I34),0)) entered as an array function (with ctrl-shft-enter). The MATCH function locates the first nonblank entry and returns its index number so INDEX knows where to get the first non-blank entry.

I'm sure there are other approaches that will work, too.

**daljaxon** · 01-25-2007, 08:12 PM

Thanks to both of you.

WOW, it is obvious that I need to follow links available on this forum and learn more advance featurse on Excel. I see now that what I know is merely only a scratch on what I need to know.

Pinmaster, your formula is a little too advance for me to understand right now. So looking at it and trying to modify it for my application will have to wait until I learn more.

MrShorty, your formula still uses functions I need to study up on to understand on how it works, but this looks more close to my needs.

I am still a simplton in using Excel and I will study more on it usage to get a better understanding of formulas that you two have provided me.

Thanks again,
daljaxon

pinmaster · 01-26-2007, 12:37 AM

Hi,

Have a look at these links:

http://www.contextures.com/tiptech.html

http://www.contextures.com/xlFunctio...tml#SumProduct

http://www.contextures.com/xlFunctions03.html

Regards!
Jean-Guy

**daddylonglegs** · 01-26-2007, 06:22 AM

I don’t really follow the logic. When are the mileage readings taken, at the same time as the truck is re-fuelled?

Why exclude any of these readings, if you want a complete picture, say for December, wouldn’t you subtract the first mileage reading for December from the first in January and divide by the total gallons added in December? (although, of course that doesn’t correspond exactly with a calendar month)

If you’re trying to find the average MPG from the first re-fuelling in a month to the last I’d suggest that you’d need to exclude the LAST entry (gallons added) for the month not the FIRST but then you’d always be ignoring the period between the last re-fuelling of one month and the first of the next.

Of course all of the above assumes that the truck will be at or near to empty when re-fuelling takes place…..

**daljaxon** · 01-26-2007, 08:58 AM

Hi Daddylonglegs,

...and all depends too, on whether or not you fuel up completely at the start and at the end of each reading if you want a very accurate reading.

I require all employees to get the mileage at each fueling as according to what the owners require.

In so doing so, the vehicle is fueled up when I get the base mileage value at the start of the month, so I don't need the first gallon amount because it has not been used yet.

At the end of the month, when the employee fills-up, I get the ending mileage to subtract the base mileage to get how many miles there were between fill-ups. Then I would use the last gallon entry, because this shows me how many gallons were used.

Example,
At the start of the month, you put in 10 gallons to fill up. In the month you only drive 100 miles. You fill up and find it only takes 5 gallons to fill up. That means you used 5 gallons at the rate of 20 mpg. Now if you used your logic, you would show 10 mpg, and that really isn't correct. I think you are looking at it backwards. Sorry!

Here I can get an accurate mpg for the vehicle. Of course, all this depends on if the employees fill-up completely and not just put in $20 all the time to get a very good reading.

Hey Pinmaster,

Thanks for your links.

I appreciate everyone's comments!

Daljaxon

**daljaxon** · 01-26-2007, 02:12 PM

Hey MrShorty,

Using your fomula, modifying it slightly, I was able to get my Vehicle Status Report workbook working and doing what I what I wanted it to do with minimum input from myself.

Thanks again,
daljaxon

Is there a function that returns the first value in an array?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a function that returns the first value in an array?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions