Formulas 1) to return the date of the first occurrence of the highest value; and 2) to count the number of occurrences

benejindu

Board Regular
Joined
Feb 17, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Gentlemen,

I have a table that records for each day, the sales quantities of different products. The record is updated every day. The list will continue to be updated. @Fluff has helped me with a furmula that returns the highest sale quantity (of each of the products) that occurred within the prior year from each day.

Kindly help with:
1) A formula that would return the date that the highest value first occurred.
2) Count the number of times the highest value occurred.
stocke.xlsx
ABCDE
1DateCompany Close 52 Weeks High 52 Weeks Low
211/1/2022FLOURMILL30.30
311/1/2022ZENITHBANK20.00
411/2/2022FLOURMILL30.30
511/2/2022ZENITHBANK19.95
611/3/2022FLOURMILL30.30
711/3/2022ZENITHBANK19.90
811/4/2022FLOURMILL30.30
911/4/2022ZENITHBANK19.85
1011/7/2022FLOURMILL30.30
1111/7/2022ZENITHBANK20.00
1211/8/2022FLOURMILL30.30
1311/8/2022ZENITHBANK19.90
1411/9/2022FLOURMILL30.30
1511/9/2022ZENITHBANK19.95
1611/10/2022FLOURMILL30.30
1711/10/2022ZENITHBANK19.90
1811/11/2022FLOURMILL27.30
1911/11/2022ZENITHBANK19.95
2011/14/2022FLOURMILL27.30
2111/14/2022ZENITHBANK19.95
2211/15/2022FLOURMILL27.30
2311/15/2022ZENITHBANK20.10
2411/16/2022FLOURMILL27.30
2511/16/2022ZENITHBANK21.80
2611/17/2022FLOURMILL27.30
2711/17/2022ZENITHBANK21.30
2811/18/2022FLOURMILL27.30
2911/18/2022ZENITHBANK21.35
3011/21/2022FLOURMILL27.30
3111/21/2022ZENITHBANK21.70
3211/22/2022FLOURMILL27.30
3311/22/2022ZENITHBANK22.10
3411/23/2022FLOURMILL27.30
3511/23/2022ZENITHBANK22.70
3611/24/2022FLOURMILL27.30
3711/24/2022ZENITHBANK22.00
3811/25/2022FLOURMILL28.10
3911/25/2022ZENITHBANK21.90
4011/28/2022FLOURMILL28.10
4111/28/2022ZENITHBANK22.00
4211/29/2022FLOURMILL28.10
4311/29/2022ZENITHBANK21.70
4411/30/2022FLOURMILL28.10
4511/30/2022ZENITHBANK21.90
4612/1/2022FLOURMILL28.10
4712/1/2022ZENITHBANK22.75
4812/2/2022FLOURMILL28.10
4912/2/2022ZENITHBANK22.75
5012/5/2022FLOURMILL28.10
5112/5/2022ZENITHBANK22.20
5212/6/2022FLOURMILL28.10
5312/6/2022ZENITHBANK22.05
5412/7/2022FLOURMILL27.60
5512/7/2022ZENITHBANK22.00
5612/8/2022FLOURMILL27.60
5712/8/2022ZENITHBANK22.05
5812/9/2022FLOURMILL27.60
5912/9/2022ZENITHBANK22.10
6012/12/2022FLOURMILL27.60
6112/12/2022ZENITHBANK22.45
6212/13/2022FLOURMILL27.60
6312/13/2022ZENITHBANK23.00
6412/14/2022FLOURMILL27.60
6512/14/2022ZENITHBANK23.00
6612/15/2022FLOURMILL27.60
6712/15/2022ZENITHBANK23.50
6812/16/2022FLOURMILL27.60
6912/16/2022ZENITHBANK24.10
7012/19/2022FLOURMILL27.60
7112/19/2022ZENITHBANK24.00
7212/20/2022FLOURMILL27.60
7312/20/2022ZENITHBANK24.25
7412/21/2022FLOURMILL27.60
7512/21/2022ZENITHBANK24.30
7612/22/2022FLOURMILL27.60
7712/22/2022ZENITHBANK24.10
7812/23/2022FLOURMILL27.60
7912/23/2022ZENITHBANK24.55
8012/28/2022FLOURMILL27.60
8112/28/2022ZENITHBANK24.90
8212/29/2022FLOURMILL28.40
8312/29/2022ZENITHBANK24.90
8412/30/2022FLOURMILL28.40
8512/30/2022ZENITHBANK24.00
861/3/2023FLOURMILL28.40
871/3/2023ZENITHBANK24.00
881/4/2023FLOURMILL30.00
891/4/2023ZENITHBANK24.10
901/5/2023FLOURMILL30.00
911/5/2023ZENITHBANK24.55
921/6/2023FLOURMILL29.90
931/6/2023ZENITHBANK24.45
941/9/2023FLOURMILL29.00
951/9/2023ZENITHBANK24.30
961/10/2023FLOURMILL29.00
971/10/2023ZENITHBANK24.30
981/11/2023FLOURMILL29.00
991/11/2023ZENITHBANK24.40
1001/12/2023FLOURMILL29.00
1011/12/2023ZENITHBANK25.10
1021/13/2023FLOURMILL30.00
1031/13/2023ZENITHBANK25.55
1041/16/2023FLOURMILL30.00
1051/16/2023ZENITHBANK24.60
1061/17/2023FLOURMILL30.90
1071/17/2023ZENITHBANK24.60
1081/18/2023FLOURMILL30.90
1091/18/2023ZENITHBANK24.60
1101/19/2023FLOURMILL30.90
1111/19/2023ZENITHBANK24.55
1121/20/2023FLOURMILL30.90
1131/20/2023ZENITHBANK24.45
1141/23/2023FLOURMILL30.90
1151/23/2023ZENITHBANK24.45
1161/24/2023FLOURMILL30.90
1171/24/2023ZENITHBANK24.50
1181/25/2023FLOURMILL30.90
1191/25/2023ZENITHBANK24.50
1201/26/2023FLOURMILL30.90
1211/26/2023ZENITHBANK24.70
1221/27/2023FLOURMILL30.90
1231/27/2023ZENITHBANK24.70
1241/30/2023FLOURMILL30.90
1251/30/2023ZENITHBANK24.90
1261/31/2023FLOURMILL30.90
1271/31/2023ZENITHBANK25.10
1282/1/2023FLOURMILL30.00
1292/1/2023ZENITHBANK25.50
1302/2/2023FLOURMILL30.00
1312/2/2023ZENITHBANK25.50
1322/3/2023FLOURMILL30.00
1332/3/2023ZENITHBANK25.10
1342/6/2023FLOURMILL29.95
1352/6/2023ZENITHBANK25.10
1362/7/2023FLOURMILL29.95
1372/7/2023ZENITHBANK25.00
1382/8/2023FLOURMILL29.95
1392/8/2023ZENITHBANK25.20
1402/9/2023FLOURMILL29.95
1412/9/2023ZENITHBANK25.10
1422/10/2023FLOURMILL29.95
1432/10/2023ZENITHBANK25.10
1442/14/2023FLOURMILL30.00
1452/14/2023ZENITHBANK25.30
1462/15/2023FLOURMILL30.00
1472/15/2023ZENITHBANK25.35
1482/16/2023FLOURMILL30.00
1492/16/2023ZENITHBANK25.50
1502/17/2023FLOURMILL30.00
1512/17/2023ZENITHBANK25.50
1522/20/2023FLOURMILL30.90
1532/20/2023ZENITHBANK25.00
1542/21/2023FLOURMILL30.90
1552/21/2023ZENITHBANK25.00
1562/22/2023FLOURMILL31.00
1572/22/2023ZENITHBANK25.20
1582/23/2023FLOURMILL31.00
1592/23/2023ZENITHBANK25.35
1602/24/2023FLOURMILL32.05
1612/24/2023ZENITHBANK25.75
1622/28/2023FLOURMILL32.00
1632/28/2023ZENITHBANK26.60
1643/1/2023FLOURMILL32.00
1653/1/2023ZENITHBANK26.65
1663/2/2023FLOURMILL32.00
1673/2/2023ZENITHBANK26.40
1683/3/2023FLOURMILL33.50
1693/3/2023ZENITHBANK26.60
1703/6/2023FLOURMILL32.95
1713/6/2023ZENITHBANK26.30
1723/7/2023FLOURMILL32.95
1733/7/2023ZENITHBANK25.95
1743/8/2023FLOURMILL32.95
1753/8/2023ZENITHBANK25.95
1763/9/2023FLOURMILL32.95
1773/9/2023ZENITHBANK26.00
1783/10/2023FLOURMILL32.95
1793/10/2023ZENITHBANK25.80
1803/13/2023FLOURMILL32.95
1813/13/2023ZENITHBANK25.00
1823/14/2023FLOURMILL32.95
1833/14/2023ZENITHBANK25.00
1843/15/2023FLOURMILL31.00
1853/15/2023ZENITHBANK23.85
1863/16/2023FLOURMILL31.00
1873/16/2023ZENITHBANK23.85
1883/17/2023FLOURMILL31.00
1893/17/2023ZENITHBANK24.45
1903/20/2023FLOURMILL31.00
1913/20/2023ZENITHBANK24.75
1923/21/2023FLOURMILL31.00
1933/21/2023ZENITHBANK25.10
1943/22/2023FLOURMILL31.00
1953/22/2023ZENITHBANK25.10
1963/23/2023FLOURMILL31.00
1973/23/2023ZENITHBANK25.10
1983/24/2023FLOURMILL31.00
1993/24/2023ZENITHBANK24.95
2003/27/2023FLOURMILL31.00
2013/27/2023ZENITHBANK25.00
2023/28/2023FLOURMILL31.00
2033/28/2023ZENITHBANK25.00
2043/29/2023FLOURMILL31.00
2053/29/2023ZENITHBANK25.00
2063/30/2023FLOURMILL31.00
2073/30/2023ZENITHBANK25.90
2084/3/2023FLOURMILL31.00
2094/3/2023ZENITHBANK25.80
2104/4/2023FLOURMILL31.00
2114/4/2023ZENITHBANK25.15
2124/5/2023FLOURMILL29.50
2134/5/2023ZENITHBANK25.50
2144/6/2023FLOURMILL29.50
2154/6/2023ZENITHBANK25.50
2164/11/2023FLOURMILL29.50
2174/11/2023ZENITHBANK25.50
2184/12/2023FLOURMILL29.50
2194/12/2023ZENITHBANK25.50
2204/13/2023FLOURMILL29.50
2214/13/2023ZENITHBANK25.05
2224/14/2023FLOURMILL29.50
2234/14/2023ZENITHBANK25.00
2244/17/2023FLOURMILL29.50
2254/17/2023ZENITHBANK22.00
2264/18/2023FLOURMILL29.70
2274/18/2023ZENITHBANK21.80
2284/19/2023FLOURMILL29.70
2294/19/2023ZENITHBANK21.85
2304/20/2023FLOURMILL29.70
2314/20/2023ZENITHBANK21.95
2324/25/2023FLOURMILL29.70
2334/25/2023ZENITHBANK22.05
2344/26/2023FLOURMILL29.70
2354/26/2023ZENITHBANK22.55
2364/27/2023FLOURMILL29.70
2374/27/2023ZENITHBANK22.80
2384/28/2023FLOURMILL31.00
2394/28/2023ZENITHBANK22.80
2405/2/2023FLOURMILL31
2415/2/2023ZENITHBANK23.5
2425/3/2023FLOURMILL31
2435/3/2023ZENITHBANK24
2445/4/2023FLOURMILL31
2455/4/2023ZENITHBANK24
2465/5/2023FLOURMILL31
2475/5/2023ZENITHBANK24
2485/8/2023FLOURMILL31
2495/8/2023ZENITHBANK24.05
2505/9/2023FLOURMILL31.1
2515/9/2023ZENITHBANK24.5
2525/10/2023FLOURMILL31.1
2535/10/2023ZENITHBANK24.4
2545/11/2023FLOURMILL31.1
2555/11/2023ZENITHBANK23.9
2565/12/2023FLOURMILL31.1
2575/12/2023ZENITHBANK24
2585/15/2023FLOURMILL31.1
2595/15/2023ZENITHBANK24.15
2605/16/2023FLOURMILL31.05
2615/16/2023ZENITHBANK24.5
2625/17/2023FLOURMILL31.2
2635/17/2023ZENITHBANK25.15
2645/18/2023FLOURMILL31
2655/18/2023ZENITHBANK25
2665/19/2023FLOURMILL31
2675/19/2023ZENITHBANK26
2685/22/2023FLOURMILL31
2695/22/2023ZENITHBANK26.45
2705/23/2023FLOURMILL31.1
2715/23/2023ZENITHBANK27
2725/24/2023FLOURMILL31.1
2735/24/2023ZENITHBANK26.75
2745/25/2023FLOURMILL31.8
2755/25/2023ZENITHBANK26.75
2765/26/2023FLOURMILL31.3
2775/26/2023ZENITHBANK27
2785/30/2023FLOURMILL33.5
2795/30/2023ZENITHBANK29.7
2805/31/2023FLOURMILL34.8
2815/31/2023ZENITHBANK29
Sheet2
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try:

Book1
ABCDEFGHIJ
1DateCompany Close 52 Weeks High 52 Weeks Low CompaniesHighFirst time# of times
211/1/2022FLOURMILL30.3FLOURMILL34.85/31/20231
311/1/2022ZENITHBANK20ZENITHBANK29.75/30/20231
411/2/2022FLOURMILL30.3
511/2/2022ZENITHBANK19.95
611/3/2022FLOURMILL30.3
711/3/2022ZENITHBANK19.9
811/4/2022FLOURMILL30.3
911/4/2022ZENITHBANK19.85
1011/7/2022FLOURMILL30.3
1111/7/2022ZENITHBANK20
1211/8/2022FLOURMILL30.3
1311/8/2022ZENITHBANK19.9
1411/9/2022FLOURMILL30.3
1511/9/2022ZENITHBANK19.95
1611/10/2022FLOURMILL30.3
1711/10/2022ZENITHBANK19.9
1811/11/2022FLOURMILL27.3
1911/11/2022ZENITHBANK19.95
2011/14/2022FLOURMILL27.3
2111/14/2022ZENITHBANK19.95
Sheet2
Cell Formulas
RangeFormula
G2:G3G2=UNIQUE(B2:B281)
H2:H3H2=MAXIFS(C2:C281,B2:B281,G2#)
J2:J3J2=COUNTIFS(B2:B281,G2#,C2:C281,H2#)
I2:I3I2=MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
Dynamic array formulas.
 
Upvote 0
Try:

Book1
ABCDEFGHIJ
1DateCompany Close 52 Weeks High 52 Weeks Low CompaniesHighFirst time# of times
211/1/2022FLOURMILL30.3FLOURMILL34.85/31/20231
311/1/2022ZENITHBANK20ZENITHBANK29.75/30/20231
411/2/2022FLOURMILL30.3
511/2/2022ZENITHBANK19.95
611/3/2022FLOURMILL30.3
711/3/2022ZENITHBANK19.9
811/4/2022FLOURMILL30.3
911/4/2022ZENITHBANK19.85
1011/7/2022FLOURMILL30.3
1111/7/2022ZENITHBANK20
1211/8/2022FLOURMILL30.3
1311/8/2022ZENITHBANK19.9
1411/9/2022FLOURMILL30.3
1511/9/2022ZENITHBANK19.95
1611/10/2022FLOURMILL30.3
1711/10/2022ZENITHBANK19.9
1811/11/2022FLOURMILL27.3
1911/11/2022ZENITHBANK19.95
2011/14/2022FLOURMILL27.3
2111/14/2022ZENITHBANK19.95
Sheet2
Cell Formulas
RangeFormula
G2:G3G2=UNIQUE(B2:B281)
H2:H3H2=MAXIFS(C2:C281,B2:B281,G2#)
J2:J3J2=COUNTIFS(B2:B281,G2#,C2:C281,H2#)
I2:I3I2=MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
Dynamic array formulas.
Hi Eric, Thanks so much for your great help.

1) =MIN(FILTER(A$2:A$281,(B$2:B$281=G2)*(C$2:C$281=H2)))
In the actual workbook, the formula returns a date that is OLDER than 1 year from today. My objective is to get the date on which the highest value for each product occurred within one year prior to today (or any other date).

2) =COUNTIFS(B2:B281,G2#,C2:C281,H2#)
In the actual worksheet, this formula returns zero, despite the fact that there are values that meet the criteria.

Benejindu
 
Upvote 0
1) To handle a starting date, adjust the formulas as follows:

Book1
GHIJK
1CompaniesHighFirst time# of timesAfter:
2FLOURMILL34.85/31/202316/5/2022
3ZENITHBANK29.75/30/20231
Sheet2
Cell Formulas
RangeFormula
G2:G3G2=UNIQUE(B2:B281)
H2:H3H2=MAXIFS(C2:C281,B2:B281,G2#,A2:A281,">"&K2)
J2:J3J2=COUNTIFS(B2:B281,G2#,C2:C281,H2#,A2:A281,">"&K2)
K2K2=TODAY()-365
I2I2=MIN(FILTER(A$2:A$281,(A$2:A$281>K2)*(B$2:B$281=G2)*(C$2:C$281=H2)))
I3I3=MIN(FILTER(A$2:A$281,(B$2:B$281=G3)*(C$2:C$281=H3)))
Dynamic array formulas.


You can incorporate the date in the formulas if you want.

2) It should return at least one, since it should match the maximum value found. As for the rest, it's possible you're encountering the dreaded binary/decimal rounding error problem. In short, Excel stores numbers in a binary format (all 1s and 0s), but displays them in a decimal format. Integers work fine, decimals often are rounded in about the 14th digit which makes it so there's no match. There are several ways to handle this, but probably the easiest is just to count the numbers that are "close enough". You seem to be showing 2 decimals, so if we count anything that is within .01, then that should match what you'd see if you counted them by eye. Change the J2 formula to:

Excel Formula:
=COUNTIFS(B2:B281,G2#,C2:C281,">"&H2#-0.01,A2:A281,">"&K2)
 
Upvote 1
Solution
1) To handle a starting date, adjust the formulas as follows:

Book1
GHIJK
1CompaniesHighFirst time# of timesAfter:
2FLOURMILL34.85/31/202316/5/2022
3ZENITHBANK29.75/30/20231
Sheet2
Cell Formulas
RangeFormula
G2:G3G2=UNIQUE(B2:B281)
H2:H3H2=MAXIFS(C2:C281,B2:B281,G2#,A2:A281,">"&K2)
J2:J3J2=COUNTIFS(B2:B281,G2#,C2:C281,H2#,A2:A281,">"&K2)
K2K2=TODAY()-365
I2I2=MIN(FILTER(A$2:A$281,(A$2:A$281>K2)*(B$2:B$281=G2)*(C$2:C$281=H2)))
I3I3=MIN(FILTER(A$2:A$281,(B$2:B$281=G3)*(C$2:C$281=H3)))
Dynamic array formulas.


You can incorporate the date in the formulas if you want.

2) It should return at least one, since it should match the maximum value found. As for the rest, it's possible you're encountering the dreaded binary/decimal rounding error problem. In short, Excel stores numbers in a binary format (all 1s and 0s), but displays them in a decimal format. Integers work fine, decimals often are rounded in about the 14th digit which makes it so there's no match. There are several ways to handle this, but probably the easiest is just to count the numbers that are "close enough". You seem to be showing 2 decimals, so if we count anything that is within .01, then that should match what you'd see if you counted them by eye. Change the J2 formula to:

Excel Formula:
=COUNTIFS(B2:B281,G2#,C2:C281,">"&H2#-0.01,A2:A281,">"&K2)
Thank you so much @Eric W ! It worked!
One thing, though: I don't understand why you had to put the # symbol. The COUNTIFS wouldn't work till I removed # from the formula. Please, what's the # supposed to do?
Benejindu
 
Upvote 0
Starting a few years back, Microsoft introduced some dynamic array functions, like UNIQUE. If you use one of them, and the results take more than one cell, the results will SPILL down the column as far as necessary. If you want to refer to the entire array of results, you reference the cell with the formula in it, and the put the # on the end. So the G2 formula has 2 results, so G2# refers to G2:G3. The real benefit of this is if you use the array form (G2#) in other formulas, then they will SPILL also. I don't know why it didn't work for you. You can still do things the old fashioned way though, which is what you ended up doing. For example:

Book1
GHIJK
1CompaniesHighFirst time# of timesAfter:
2FLOURMILL34.85/31/202316/5/2022
3ZENITHBANK29.75/30/20231
Sheet2
Cell Formulas
RangeFormula
G2:G3G2=UNIQUE(B2:B281)
H2:H3H2=MAXIFS(C$2:C$281,B$2:B$281,G2,A$2:A$281,">"&K$2)
I2:I3I2=MIN(FILTER(A$2:A$281,(A$2:A$281>K$2)*(B$2:B$281=G2)*(C$2:C$281>H2-0.01)))
J2:J3J2=COUNTIFS(B$2:B$281,G2,C$2:C$281,">"&H2-0.01,A$2:A$281,">"&K$2)
K2K2=TODAY()-365
Dynamic array formulas.


2 things to keep in mind, If you do it this way, you need to drag the formulas down the column yourself as far as needed. And second, if you do that, then Excel will automatically update the range references for you, so you'll need to use the $ symbol to make sure the ranges in the formulas stay the same as you drag them down. See how I did it above. (I also tweaked the I2 formula a bit to handle an issue I just thought of.)

Glad I could help!
 
Upvote 1

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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