Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup / max & min functions
I am trying to get a min and a max value in column C for the various
Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup / max & min functions
Use F1, say, to enter the month you are interested in (as a number 1
to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57*am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help * * * *A-DATE * * * *B-TRUCK REG * * * *C-KILOMETERS * * * * D-DIESEL FILLED 1 * * 1/5/08 * * * * *JWY467GP * * * * * * 10000 * * * * * * * * * * *1000 * 2 * * 5/5/08 * * * * *PTS030GP * * * * * * *5000 * * * * * * * * * * * 500 3 * * 6/5/08 * * * * *MVT537GP * * * * * * 2000 * * * * * * * * * * * 250 4 * * 7/5/08 * * * * *JWY467GP * * * * * * 12000 * * * * * * * * * * *1000 5 * * 10/5/08 * * * *PTS030GP * * * * * * *6000 * * * * * * * * * * * 500 6 * * 12/05/08 * * *MVT537GP * * * * * * *2500 * * * * * * * * * * *250 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup / max & min functions
Hi Pete
Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup / max & min functions
You're welcome, Robbie - thanks for feeding back.
Pete On May 6, 8:17*am, Robbie wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup / max & min functions
Good Day
I am currently using the below formula for min and max value by month(). I would like to go a step further now and recover the min and max value between 2 date values. ie. diesel used between January to April Appreciate the help "Robbie" wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup functions | Excel Discussion (Misc queries) | |||
lookup functions | Setting up and Configuration of Excel | |||
Lookup functions | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions | |||
LOOKUP functions? | Excel Worksheet Functions |