Alexandra12
New Member
- Joined
- May 29, 2008
- Messages
- 33
Hello Guys,
Does anyone know what the formula is to get the max value from the below
I need to look up the date and hour in 1 cell and give me back the highest number for that DATE AND THAT HOUR in another table.
At the moment I tried this, but it does not help :
=VLOOKUP(B2,'1245'!A:F,MAX('1245'!F:F)*6,0)
I also tried =vlookup(max(
but it gives me back the 1st result for that hour and not the highest value.
Here is data from the sheet where I need to have my results
B D
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl18 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 98pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131 height=34> Hourly Data</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl17 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39588">"0/05/2008 00:00</TD></TR></TBODY></TABLE>
Here is the data from the other sheet that I am doing the Vlookup on to get the highest number :
The data in column D is automatically pasted in while gathering the data due to the format it comes in, so I would prefer to leave this as is if possible and for the lookup to compare against the truncated date and hour in column A
A D E F<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=379 border=0 x:str><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 115pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=153 height=17>Truncated Text</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 91pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=121>Localtimestamp</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 33pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=44>Value</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 46pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=61>Units</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568">04/30/08 00:00</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>625</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568.010416666664">04/30/08 00:15</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>608</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR></TBODY></TABLE>
Any ideas ?
Thanks in advance
Does anyone know what the formula is to get the max value from the below
I need to look up the date and hour in 1 cell and give me back the highest number for that DATE AND THAT HOUR in another table.
At the moment I tried this, but it does not help :
=VLOOKUP(B2,'1245'!A:F,MAX('1245'!F:F)*6,0)
I also tried =vlookup(max(
but it gives me back the 1st result for that hour and not the highest value.
Here is data from the sheet where I need to have my results
B D
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl18 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 98pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131 height=34> Hourly Data</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl17 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39588">"0/05/2008 00:00</TD></TR></TBODY></TABLE>
Here is the data from the other sheet that I am doing the Vlookup on to get the highest number :
The data in column D is automatically pasted in while gathering the data due to the format it comes in, so I would prefer to leave this as is if possible and for the lookup to compare against the truncated date and hour in column A
A D E F<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=379 border=0 x:str><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 115pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=153 height=17>Truncated Text</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 91pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=121>Localtimestamp</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 33pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=44>Value</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 46pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=61>Units</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568">04/30/08 00:00</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>625</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568.010416666664">04/30/08 00:15</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>608</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR></TBODY></TABLE>
Any ideas ?
Thanks in advance
Last edited: