PDA

View Full Version : Excel =MAX, =MIN from Range via VBA



Grantx
12-12-2007, 04:07 AM
Please refer to the attachment.

To extract the maximum value from column B that corresponds to a specific time, I?m using the cell formula

=MAX(IF(A$3:A$30000=726,B$3:B$30000))

(=MIN is substituted to find the minimum value).

The formula above is repeated approximately 700 times in cells/rows corresponding to each 1-minute increment (from 727, ie 7:27 am, to 1900).

I think a VBA solution would be more efficient. Perhaps someone could make a suggestion.

Thank you.

Grant.

Bob Phillips
12-12-2007, 04:27 AM
This is DEFINITELY one for a pivot table.

Grantx
12-12-2007, 09:14 AM
XLD,

Then I'll definitely give it a go. Thank you for the suggestion.

Grant.

Grantx
12-12-2007, 12:47 PM
XLD,

Correct me if I am wrong, but don't pivot tables need to calculated manually to update/refresh?

Further, are they significantly more efficient than my current approach?

I suggested vba because it will calculate only specified cells, not all cells indiscriminately.

Grant.

figment
12-12-2007, 12:53 PM
why is the equation repeted? if you could tell us what you want the end result to be we could be more helpfull.

Grantx
12-12-2007, 01:51 PM
Figment,

It is repeated because there are around 700 different potential values corresponding to minutes of the day (c 7:00 am ? 19:00 pm).

The end results with which we are concerned are the maximum (and minimum) values . These represent basic building blocks.

Is this what you meant?

Grant.

figment
12-12-2007, 02:03 PM
so in each instance this number changes?

=MAX(IF(A$3:A$30000=726,B$3:B$30000))

if this is the case i see a few ways of doing this; you can do it by seting that value as equale to a cell and cuting and pasting into 700 diffrent cells, this is the way i would do it for it would be live on the sheet and would update when ever you change the input data.

or you can us VBA the VBA will be slower and will only update when you call the code, but both will work.

you could also make a pivot table as Xld sugests.

here is an example of the three methods
7491

Grantx
12-12-2007, 05:55 PM
Figment,

I will look at the zip file but first I wanted to acknowledge your reply.

Re your question. A$3:A$300000 represents the total range. This is constant ? it never changes.

The time, 726, for example (with all the others up to 1900) are likewise fixed.

The formula in column C, {=MAX(IF(A$3:A$30000=726,B$3:B$30000))} is repeated for every increment of 1 (minute). This is for 726 (7:26am); the next cell will be the same range but next higher increment, ie {=MAX(IF(A$3:A$30000=727,B$3:B$30000))}, and so on up to {=MAX(IF(A$3:A$30000=1900,B$3:B$30000))}. I have cut and pasted these.

What does change is the range for each increment. The attachment shows the range for 726 at A$3:B$10. Tomorrow this will different ? a greater or lesser range.

As you can see in the attachment, there may be a few different values per second. I know from other sheets I use that Excel has no problem keeping up with much higher input frequencies. But the bottleneck emerges with the formulae as above when each cell is needlessly re-calculated, eg if the time is 730, the 729 equation is gone and should be ignored (but recorded because relevant).

To return to VBA. My thinking is that VBA code can determine the maximum value for one increment at a time (as opposed to calculating everything) and place the result in the last row (n+1?). The line of code is not repeated for every increment as above; maybe use some kind of a loop? It calculates whenever a new input arrives. Another sheet I use works along similar lines but is around twenty-six times the size of this, and runs flawlessly. This is because all calculations are done within the vba module. However, as you probably guessed, VBA is not my forte. And although I don?t doubt your VBA expertise, I would question the assertion that VBA will be slower in this case. Pivot tables are impractical due to the need for manual re-calculation.

Grant.

Grantx
12-13-2007, 06:41 PM
Maybe this is viable. I just need directions re implementation. Please refer to attachment.

We?re only concerned with column 7 (ignore the others). The following is a repeat of the previous formula for vba:

ExcelDoc.Sheets(1).Cells(nRow, 7).FormulaR1C1=?=MAX(IF(R3C[-6]:R30000C[-6]=RC[-1],R3C[-5]:R30000C[-5]))?

The trigger, so to speak, to put the resulting value into its respective cell (nRow, 7) is at 1,10 (726). What if we said, if the last cell in column 1= 726 (1,10), then:

ExcelDoc.Sheets(1).Cells(nRow, 7).FormulaR1C1=?=MAX(IF(R3C[-6]:R30000C[-6]=RC[-1],R3C[-5]:R30000C[-5]))?

Constantly updating with new data should be irrelevant - while data may be constantly changing as a result of fresh data it is only the last occurrence of a particular time we need (and hopefully will be entered).

Thank you.

Grant.

Bob Phillips
12-14-2007, 02:25 AM
XLD,

Correct me if I am wrong, but don't pivot tables need to calculated manually to update/refresh?

Further, are they significantly more efficient than my current approach?

I suggested vba because it will calculate only specified cells, not all cells indiscriminately.

Grant.

I thought that I had responded to this yesterday, but its not there so ....

Pivot tables do need to be refreshed manually, but it is a simple matter to either add a refresh button, or better still, add some refresh code to some worksheet event, thereby forcing a refresh.

And yes, because it is a flagship function in Excel, because it is written in low-level code, because it is written by MS coders who know things about Excel and Windows that the rest of the world do not, because it is the core of Excel's BI offering, it is VERY efficient, bettre than anything you or I could knock up. Have you ever seen a pivot of 1 couple of million rows of an external database, it is impressive.

VBA will be slow no matter how restrictive it is, no matter how tight the code. An XLL would be better, but that is a whole new paradigm.

Grantx
12-15-2007, 01:02 PM
XLD,

Ok, I'll give it try. Now I'm curious about XLL. Thank you for the suggestion.

Grant.

Bob Phillips
12-15-2007, 02:31 PM
A bit more info her http://www.vbaexpress.com/forum/showthread.php?t=16484&highlight=compiled