Macro to do calculations with sets of data

[flanna]

Can anyone help with this?
The data is numerical data in a column(columnB). The column receives live data updates so it cannot be sorted in numerical order. ColumnA holds time stamps and must remain in original order All the numbers are between 0.1 and 99.9.
I need to take sets of numbers (i.e. >0 AND <10, >20 AND <=25 etc.) and find SUM, COUNT, STDEV for the last x numbers in each group.
x can be a different value for each group.
I think this can only be achieved with a macro. I am using Excel2016.

 

[James006]

Hi,

With an event macro ... such as Private Sub Worksheet_Change(ByVal Target As Range) you could :

1. If needed, perform an automatic sort ...
2. Refresh all your sum-count-standard deviation formulas ... automatically ...

HTH

 

[flanna]

Hi,

With an event macro ... such as Private Sub Worksheet_Change(ByVal Target As Range) you could :

1. If needed, perform an automatic sort ...
2. Refresh all your sum-count-standard deviation formulas ... automatically ...

HTH

Thanks James, but what I really need to do is something like this: Find last 20 items from the end of the column where value is more than 5 and less than 10, than using the found items apply various formulas and show results in predefined cell. This has to be repeated each time new data is added to the sheet.

 

[James006]

Hi,

There is no contradiction at all ... between your latest message ... and what your event macro can perform ...

If you want to see what event macros are ... https://www.youtube.com/watch?v=ZnnCm7tyqlg

HTH

 

[flanna]

Hi,

There is no contradiction at all ... between your latest message ... and what your event macro can perform ...

If you want to see what event macros are ... https://www.youtube.com/watch?v=ZnnCm7tyqlg

HTH

Yes, an event macro will be the right way to go. But what I really need to know is how to write the rest of the macro. Once I have a working macro it will be easy to make it into an event macro.

 

[James006]

...what I really need to know is how to write the rest of the macro ...

Does it mean you could share your current macro ...?

 

[flanna]

Does it mean you could share your current macro ...?

What current macro? I don't have one yet. That's why I am asking for help to write one.

 

[James006]

I need to take sets of numbers (i.e. >0 AND <10, >20 AND <=25 etc.) and find SUM, COUNT, STDEV for the last x numbers in each group.
x can be a different value for each group.

Back to your first message ... unfortunately all your constraints (input and output) are too vague to write a macro ...

My recommendation would be to turn on your macro recorder and go through one process from start to end ... in order to get a basic structure ...

HTH