Excel question

[Coytee]

Let's say you have a column of numbers.  You have 2,000 pieces of data.

 

Can you and if you can, how would you:

 

Create a formula for the next column.  This formula would find (on a dynamic basis) the highest value in the column of data and/or (for yet another column) it would find the lowest value in that same column.

 

Without getting into sordid details, I have a column of data that represents my weight which I track on a daily basis.

 

Just out of curiosity, I'm looking to create a column next to it that would find the highest value (and another column that would find the lowest value) so that I can have a column of data that would indicate how far I'm down from my peak weight AND, how far up I am from my lowest weight.

 

So, if current weight is for example, 150 and peak is 182 and lowest is 140, I'd want it to find those numbers (which would of course change were I to eclipse them).

 

So it finds the high/low and then posts a value that says (at weight of 150), I am down 17.5% from the peak however, I'm up 8.5% from the low.

 

I can easily find the high/low...but if it were to change, I want it to find it automatically and update the values.

 

I have no clue, can it be done?

 

 

 

[Coytee]

As a rhetorical question for Mr. JB, which would weigh more on Pluto, a pound of feathers or a pound of lead?

 

[ryanm84]

If you're working in a single column, use the min max function in excel. Column A has your data. Go to B1 and hit "=min(a1:ax) where x equals your last datapoint.

do the same for max. You should see what you're looking for.

[WMcD]

Never mind.

 

 

 

WMcD

 

 

[wvu80]

I'm going to give you a vague answer because I haven't done Excel in a while.

 

I wouldn't add a second column, I would insert a chart.  I think there is a chart that will dynamically change relative to the values in Column A and will list Hi/Lo values, as is used in tracking hi/lo values when tracking a stock's historical price points.

[ryanm84]

If you make your functions include cells that aren't yet filled out you should still be ok. In other words you have data in A15 but will be compiling thru a45 you should still show min max.

[ryanm84]

Mind you I'm just responding on my iPad. If I were at my computer I'd be happy to throw this together for you.

[Coytee]

I have a chart.  To tell another part of the story...  a long time ago I posted an image of "a chart" and used it like a stock chart....asking for others opinion as to should I buy/sell or hold.  The general opinion was sell as it was a dog.  Well, it was a chart of my weight dropping.  I decided to not keep any conversations going on that thread as I ended up feeling as though someone might get offended that I presented it in that manor.

 

I digress.

 

I do have a chart.  It doesn't tell me numerically where I am relative (mainly) to my low point.

 

I tried the min and max functions but it seems they only sort through 255 data points and I have many more than that.

 

I already KNOW the numbers, it's not hard to find them so I have cobbed the formula together simply using the high and low numbers.  To be frank, I'll probably never reach the high number again (that isn't a major issue) and I'll certainly have warning if I get near the low number again, allowing me to update either as necessary.

 

I'm just trying to automate things.

 

For some context:

 

Column A: date

Column B: weight

Column C: change of weight relative to prior day

Column D: Change of column C as a percent

Column E: "Min weight value" (since I don't have a formula, I have a static number)

Column F: Difference as a percent of my current weight relative to my minimum weight, e.g. as a percent, how much have I gained from the low point

Column G: "Max weight value" (also static number)

Column H: Difference as a percent of my current weight relative to the most I weighed, e.g. as a percent, how far am I down from the high point.

 

 

[Paducah Home Theater]

If you knew how to code you could get around the 255 row limit with a macro.  Basically any time you entered a new row or changed something, a macro would fire which would calculate the high, low, or whatever, for an entire column, then store them in a separate cell.  You would then reference that cell during your percentage calculations rather than referencing a stock formula such as MAX() and whatnot on every row.  Pretty easy concept but it would take awhile if you're not used to programming in Office.  

 

Look at the answer on this page:

http://stackoverflow.com/questions/25573689/excel-vba-to-run-macro-in-new-row-is-inserted

 

notice:

Private Sub Worksheet_Change(ByVal Target As Range)

 

At that point all you have to do is loop over the column values and do your calculations.  

 

 

[Jeff Matthews]

On 9/5/2016 at 6:19 AM, Coytee said:

I tried the min and max functions but it seems they only sort through 255 data points and I have many more than that.

I have the latest version of Excel in Office 365 On-line.  I tested it, and there is no 255 data point limit for Min(a1:ax).

[Coytee]

I've got version 2010,

[Jeff Matthews]

1 hour ago, Coytee said:

I've got version 2010,

Okay.  So, why not sort by the column in question and use the first entry for Min?

[DrWho]

Pick the cell where you want the result, and type the following for the minimum value:

=MIN(A1:A10)

 

In a different cell, type the following for the maximum value:

=MAX(A1:A10)

 

Change the values of A1 and A10 to be the cells where you're storing your data.

 

 

 

 

[DrWho]

3 hours ago, Jeff Matthews said:

Okay.  So, why not sort by the column in question and use the first entry for Min?

If you do that approach, then make sure you also add an index column....that way you can always get your results sorted back into their original order.

[twk123]

5 hours ago, MetropolisLakeOutfitters said:

If you knew how to code you could get around the 255 row limit with a macro.  Basically any time you entered a new row or changed something, a macro would fire which would calculate the high, low, or whatever, for an entire column, then store them in a separate cell.  You would then reference that cell during your percentage calculations rather than referencing a stock formula such as MAX() and whatnot on every row.  Pretty easy concept but it would take awhile if you're not used to programming in Office.  

 

Look at the answer on this page:

http://stackoverflow.com/questions/25573689/excel-vba-to-run-macro-in-new-row-is-inserted

 

notice:

Private Sub Worksheet_Change(ByVal Target As Range)

 

At that point all you have to do is loop over the column values and do your calculations.  

 

 

Once you figure out Macros in excel you never go back. I have created a whole set of "Machines" that scrape Group Health Data for my job and I can hang out on youtube while excel works far faster than I ever could by hand. Good stuff.

 

For an easy solution, I would do as Dr Who suggested. Simply create a separate column with the date then sort by the weight to get highest and lowest. The best solution is to highlight the top row then click "Filter". Click on the drop down for the column you want (Weight) then sort lowest to highest then highest to lowest. When you are done, simply do the same with the Date Column or create an index to get back to where you started. Here is a screen shot to show the data then the filter:

 

[DrWho]

On 9/5/2016 at 7:19 PM, Coytee said:

I tried the min and max functions but it seems they only sort through 255 data points and I have many more than that.

That shouldn't be the case...I've got huge files where I use Min/Max on Excel 2010 without this problem. Something on the order of 40,000 entries.... I've since moved to Matlab (there are free alternatives though like Scilab) because my files are getting up into the 200k entry range, which is too big for Excel. It can only do 65k max.

 

What kind of file format are you saving your file in? Maybe try as Save As, give it a different name, and use the latest format (.xlsx).

[BusaDude]

On 09/05/2016 at 6:19 AM, Coytee said:

I do have a chart.  It doesn't tell me numerically where I am relative (mainly) to my low point.

 

I tried the min and max functions but it seems they only sort through 255 data points and I have many more than that.

 

I already KNOW the numbers, it's not hard to find them so I have cobbed the formula together simply using the high and low numbers.  To be frank, I'll probably never reach the high number again (that isn't a major issue) and I'll certainly have warning if I get near the low number again, allowing me to update either as necessary.

 

I'm just trying to automate things.

 

For some context:

 

Column A: date

Column B: weight

Column C: change of weight relative to prior day

Column D: Change of column C as a percent

Column E: "Min weight value" (since I don't have a formula, I have a static number)

Column F: Difference as a percent of my current weight relative to my minimum weight, e.g. as a percent, how much have I gained from the low point

Column G: "Max weight value" (also static number)

Column H: Difference as a percent of my current weight relative to the most I weighed, e.g. as a percent, how far am I down from the high point.

 

 

There is no 255 line or data point limit and there's no reason to break out the VBA Macro coding for a relatively simple problem.

 

At most you'll have 365 daily weights, unless your weighing multiple times daily. And you could break out the yearly figures on individual sheets, both of which are easily handled within Excel's sheets, rows and columns limits.

 

Anyway, I did this on my phone using the mobile version of Excel. So I couldn't use range names to simply some of the formulas. But it will easily scale to however many rows you need using the absolute cell references in the formulas. And there's no reason to place the Min/Max weights over and over in each  each row... So I just put it at the top of the column and used an absolute reference in each line/formula.

 

I'm pretty sure the spreadsheet does everything you were trying to accomplish. But if not, let me know.

 

[Coytee]

15 hours ago, GPBusa said:

At most you'll have 365 daily weights

 

I appreciate yours (and everyone's) input.

 

I currently have 1,579 days worth of data.  I've been doing this for a couple years now.  (did I really say a 'couple' years?)!!!

 

If you look at the above graph, I had a pretty significant drop in my weight (peak about 185, dropping almost like a stone to 129) then, the sudden spike up.  (turns out, I had an internal infection for (guessing) 6-months.  Went in for some surgery, they drained it, put me on antibiotics and charged my wife with feeding me three times a day, a full meal with desert included if possible.

 

It was amazing to see how fast the weight jumped back on.  As you can see, it's settled down and in fact, is back on a downturn.

 

I'm not terribly concerned about this automation.  I know my low weight was 129 so if I get close to it, I can change it.

 

I made my spreadsheet similar to what you did above but rather than search for the high/low, I just did it manually.

 

My last time at that weight (185) was 4/23/13 (and admittedly, it was probbaly 20 pounds too many!)

By (on) 9/12/2014, I hit 129 pounds so in approximately 17 months, I dropped 56 pounds, against my will.

 

Been trying to keep it or add to it since.  I'll admit, it's kind of nice being able to eat a pint of your favorite ice cream every other day....  eat bags & bags of almond Hershey nuggets, milk shakes, brownies....  and not give it too much thought with regard to calories.  My gastro-doc has a nice sense of humor.  When we discussed this, he (intentionally) gave me the under his breath comment of "Must be nice to be able to eat all the deserts you want to eat and not gain a pound.........bastard........"