How to capture highest and lowest value for the day which is formula based individual cell



In attached sheet, The cell D5:D13, Can we capture the highest percentage and lowest percentage achieved for the day.





Please see the comments mention in excel sheet.

In a particular Cell i.e D5, at particular time highest percentage might achieved 105% and lowest percentage might achieved 95%.

Can we capture or record the highest % and Lowest %.




Thank you for your responses

Sorry for the communication error from my side.

Please find attached sheet, the result i am looking for (please see the comments).

Hope you understand the requirement and Please help for the solution.






Once again you have completely changed the question (the commment added to D5 under Revision 1 is at odds with the question posed in the yellow merged cell). I don't have the time to answer moving questions.
John_Ru (rep: 1002) Mar 23, '21 at 6:51 am
Hi John,
My intention and requirement both was the same at the first place.
In Yellow merged cell, I was tried to mention D5 to D13 where i am looking for the high and low of the specific cell.
I also tried to communicate the same at second place.
Hence, Request you please understand the requirement and provide the solution.
NitilA (rep: 14) Mar 23, '21 at 1:19 pm
Please don't forget to select an answer if it worked for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1969) Mar 25, '21 at 10:24 am
Add to Discussion




This replaces my earlier answer (now deleted) and refers to your Revision 2 workbook.

One (fairly) easy solution is to use a Pivot Table- I've added one in cells F4:H7, under a heading "Today's Dashboard". I've given the steps to do that (further down this answer).

My solution also uses a Table for your time/ quantity data so adding extra rows (with the formatting) is simply a matter of typing in the row immediately below the table. You do this by:

  1. Highlighting the table then use "Insert table" to convert to a table .I also gave the table the Name "Today" under Name Manager and..
  2. used Create from Selection on header row to name the columns)
  3. Under the Table Design tab, unclick Filter Button (unless you need it).

The second advantage (especially of step 2) above is that the formula for percentage can also be automatically filled down when a new row is added. Tha's done by adding a "Calculated Column" where your cell-related formula is converted to one with structured references. Instead of your formula for D5:


I deleted your cells and added a column where D5 (and the whole column) column gets the formula:

=[@[Current day Stock until time]]/(24*([@[Stock production time]]-$B$1)/VLOOKUP([@Name],$J$5:$M$7,3,FALSE)*VLOOKUP([@Name],$J$5:$M$7,2,FALSE))

This works the same but when you add a new row (by typing just below the table, as I said), that formula is copied down (along with conditional formatting).

A slight problem with this is that the column format in a new row defaults to time (I believe this is bacause Microsoft say "When you manipulate dates, the return type of the calculated column must be set to Date and Time." so I fix that in the WorksheetChange event macro that's neeeded to overcome the downside of using a PivotTable (that normally has to be refreshed manually). I've got around that by using a WorksheetChange event macro which corrects things when a value is changed in the column of the table headed "Current day Stock until_time":

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("Current_day_Stock_until_time")) Is Nothing Then Exit Sub
' force Calculated Column format to percentage
Range("Current_c.f._historic_average_daily_stock_produced_to_time").NumberFormat = "0%"
' refresh Pivot Table

End Sub

Hope this works for you (or Variatus or others propose something better).


Creating the Pivot Table:

  1. In cell F4, insert Pivot Table with:
    •  Table/Range = Today (or select table range)
    •  Location = Existing Worksheet
  2. In PivotTable Fields, drag Name to Rows
  3. Drag "Current c.f Historic..." to Values (it will default to "Sum of..."- ignore for now)- do that a second time
  4. Pick upper Sum of and click Down arrow/Value Field Settings then
    • Change Summarize by from Sum  to "Min"
    • Click Number Format button then to change Number to Percentage and set Decimal Places to 0
  5. Repeat for lower Value but Change Summarize by to "Min"
  6. Edit column headings (using "Name " with added trailing space since a label should not match a Pivot Table field name) and adjust column widths, formatting etc.



Did that work for you? 
John_Ru (rep: 1002) Mar 25, '21 at 3:47 am
Hi John,
I really appreciate your efforts and thanks for the same, but i am really sorry to say, that it will not work for me, as i don't have repetitive rows for specific name, I have single Row for the same.

NitilA (rep: 14) Mar 27, '21 at 6:46 am

That's a pity. Clear questions are the key to understanding here. and (for me)_ the problem is that your sample file showed repeated entries of "a", "b" and "c" so I assumed you'd be getting updates (per name) throughout the day. 
John_Ru (rep: 1002) Mar 27, '21 at 8:19 am
I am really sorry John for my miscommunication.
NitilA (rep: 14) Mar 27, '21 at 9:44 am
No problem but it's hard to help if I and others don't understand what you mean
John_Ru (rep: 1002) Mar 27, '21 at 9:52 am
Hi John, 
Next time I will ensure to ask the question to the point requirement.
Please bear me this time.
Require your sincere help to solve the purpose.
Really Thank you for all your efforts.
You all are doing good job for providing solutions. Really appreciate for that.
Keep up the good work.
NitilA (rep: 14) Mar 29, '21 at 7:09 am
Thanks Nitil, I'll keep answering (when I can) but I find it hard to understand your questions sometimes. Are you using a translation tool to create your questions? Something like Google Language Tools?
John_Ru (rep: 1002) Mar 29, '21 at 7:17 am
No John, I am writing by myself.
Should i submit fresh excel sheet for better understanding.
NitilA (rep: 14) Mar 29, '21 at 7:33 am
Nitil. I won't have much time this week so suggest you write a new question, with clear text in the question (and representative examples of desired outcomes in your file perhaps). That way others might give an answer. 
John_Ru (rep: 1002) Mar 29, '21 at 8:20 am

I've seen your new question but it seems to me that you've done the opposite of what I suggested! The question is no clearer (just as short and vague as before) and the file doesn't help either (gives no meaningful example).

Sadly I suspect your won't get a good answer to an incomplete question.
John_Ru (rep: 1002) Mar 29, '21 at 8:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login