Calculate higest value per day in string of dates

I

Issie_52

New Member
Joined
Jul 22, 2013
Messages
33
Good day,

Please help!

I need to calculate the highest value per day. I need a formula for the last column.

[TABLE="width: 364"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]RATE[/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD] HIGEST
VALUE[/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-10[/TD]
[TD="align: right"]10.0533[/TD]
[TD][/TD]
[TD="align: right"]10-May[/TD]
[TD="align: right"]10.0533[/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-10[/TD]
[TD="align: right"]10.0523[/TD]
[TD][/TD]
[TD="align: right"]13-May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-13[/TD]
[TD="align: right"]10.0658[/TD]
[TD][/TD]
[TD="align: right"]14-May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-14[/TD]
[TD="align: right"]10.0565[/TD]
[TD][/TD]
[TD="align: right"]15-May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-14[/TD]
[TD="align: right"]10.1057[/TD]
[TD][/TD]
[TD="align: right"]16-May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-15[/TD]
[TD="align: right"]10.1268[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-15[/TD]
[TD="align: right"]10.1454[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-15[/TD]
[TD="align: right"]10.1563[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-16[/TD]
[TD="align: right"]10.1505[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2019-05-16[/TD]
[TD="align: right"]10.1754[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any suggestion would be greatly appreciated.

Thanks.

[TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 364"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
try


Book1
ABCDE
1DATERATEDATEHIGESTVALUE
210/05/201910.053310-May10.0533
310/05/201910.052313-May10.0658
413/05/201910.065814-May10.1057
514/05/201910.056515-May10.1563
614/05/201910.105716-May10.1754
715/05/201910.1268
815/05/201910.1454
915/05/201910.1563
1016/05/201910.1505
1116/05/201910.1754
Sheet4
Cell Formulas
RangeFormula
D2=MAXIFS($B$2:$B$11,$A$2:$A$11,C2)
 
Upvote 0
Thanks for you swift reply AlanY.

It gives me a #Name ? error

[TABLE="width: 577"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]RATE[/TD]
[TD]DATE[/TD]
[TD]HIGEST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019-05-10[/TD]
[TD]10.0533[/TD]
[TD]10-May[/TD]
[TD="align: center"]#Name ?[/TD]
[TD]MAXIFS($B$2:$B$11,$A$2:$A$11,C2)[/TD]
[/TR]
[TR]
[TD]2019-05-10[/TD]
[TD]10.0523[/TD]
[TD]13-May[/TD]
[TD="align: center"]#Name ?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 493"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
A Pivot Table.
Date in the ROWS, and the RATE in Values. Change Rate from SUM of to MAX for your calculation.
 
Upvote 0
N.B. Maxifs is a fairly recent addition to Excel.


Excel 2010
ABCDE
1DATERATEDATEHIGHEST Value
210-05-1910.053310-May10.053310.0533
310-05-1910.052313-May10.065810.0658
413-05-1910.065814-May10.105710.1057
514-05-1910.056515-May10.156310.1563
614-05-1910.105716-May10.175410.1754
715-05-1910.1268
815-05-1910.1454
915-05-1910.1563
1016-05-1910.1505
1116-05-1910.1754
1c
Cell Formulas
RangeFormula
E2=AGGREGATE(14,6,B$2:B$11/(A$2:A$11=C2),1)
D2{=MAX(IF($A$2:$A$11=C2,$B$2:$B$11,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Issie_52 said:
Thanks for you swift reply AlanY.

It gives me a #Name ? error

[TABLE="width: 577"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]RATE[/TD]
[TD]DATE[/TD]
[TD]HIGEST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019-05-10[/TD]
[TD]10.0533[/TD]
[TD]10-May[/TD]
[TD="align: center"]#Name ?[/TD]
[TD]MAXIFS($B$2:$B$11,$A$2:$A$11,C2)[/TD]
[/TR]
[TR]
[TD]2019-05-10[/TD]
[TD]10.0523[/TD]
[TD]13-May[/TD]
[TD="align: center"]#Name ?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 493"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

ok, try this array formula instead


Book1
ABCDE
1DATERATEDATEHIGESTVALUE
210/05/201910.053310-May10.0533
310/05/201910.052313-May10.0658
413/05/201910.065814-May10.1057
514/05/201910.056515-May10.1563
614/05/201910.105716-May10.1754
715/05/201910.1268
815/05/201910.1454
915/05/201910.1563
1016/05/201910.1505
1116/05/201910.1754
Sheet1
Cell Formulas
RangeFormula
D2{=MAX(IF($A$2:$A$11=C2,$B$2:$B$11))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
A "regular" formula
=sumproduct(MAX(($A$2:$A$11=C2)*($B$2:$B$11)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs
A
  • Solved
Retrieving days per month that tanks are empty using filling and emptying dates
Replies
6
Views
750
Skybluekid
Skybluekid
P
  • Question Question
Count all overlapping days from range between another set of dates
Replies
1
Views
3K
Pepperoni
P
E
  • Question Question
Networkdays formula issues
Replies
1
Views
884
JonXL
JonXL
J
  • Question Question
Count out of date record
Replies
5
Views
700
Fluff
Fluff

Forum statistics

Threads
1,224,749
Messages
6,180,731
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top