Nested if functions

[fil85]

Hi all
This is my first post, here at work i have been given the task of developing a list of median prices for property sales.

So far what i need to establish is the following

If a2 is auburn than a3 = inner west but if a2 is lane cove than a3 = lower north shore i need to do this for 39 regions.

Once this is done i need to do another if function being if a3=lower north shore than a4 = ring 1

After this is done i need to be able to point it to the right sheets ie if a2 is lane cove then a5 =median for the price cells with "lane cove" in the row.

Any help or directions where to find greater help or if excell can do this would be most appreciated.
Cheers

[fil85]

Solved most of my nested if problems, my main problem now is calculating the median of columns where a row contains a name ie a1 is price a2 is name i want it only to calculate the median if the name is johns i dont want the median of all smith entires.

[jpopovaclark]

Fil,

Try using the Vlookup function instead of nested ifs. Place a table with your 39 regions and the associated index somewhere away from your data. The Vlookup function will accept the a2 as input and produce the required a3 as output by referencing the lookup table.

I hope this helps.

Jeff P-C

[daddylonglegs]

Solved most of my nested if problems, my main problem now is calculating the median of columns where a row contains a name ie a1 is price a2 is name i want it only to calculate the median if the name is johns i dont want the median of all smith entires.

If you have prices in A1:L1 and names in A2:L2 then to calculate MEDIAN of prices where name is "johns"

=MEDIAN(IF(A2:L2="johns",A1:L1))

which must be confirmed with CTRL+SHIFT+ENTER

[fil85]

Cheers thanks for that, my next problem is am i restricted to only seven if formulas in the one?

What i have now for one of my criteria is
=IF(B2="auburn","inner west",IF(B2="lane cove","north shore",IF(B2="ashfield","inner west",IF(B2="bankstown","outer west",IF(B2="botany bay","south",IF(B2="manly","northen beaches",IF(B2="north sydney","North shore",IF(B2="pittwater","northen beaches"))))))))
However it wont let me add in any more.

Also i have the following for my median problem
=IF(B2="ashfield",MEDIAN(IF('Ashfield 01'!D2:D1363="Ashfield",IF('Ashfield 01'!A2:A1363>0,'Ashfield 01'!F2:F1363)))) However i need it to look over more than one sheet but when i try
=if(b2="ashfield",median(if('ashfield 01'!D2:D1363,'Ashfield 02'D2:D2500="Ashfield" etc i have no luck.

Is there a better way than righting long if functions ? And am i limited at 7 nested ifs or can i get around this.

Cheers
Pete

[MSP77079]

There is a better way, I think. Although, one could say it is a matter of opinion.

You could have a take somewhere else that looks like this:
auburn --- inner west
lane cove --- north shore
ashfield --- inner west
banston --- outer west
botany bay --- south
et cetera --- et cetera

Then do a lookup (use VLOOKUP or MATCH+INDEX) to return the corresponding text.

If you do not like that idea at all, and you really want to stick with the nested IF statements, then at least combine them. For example,

=IF(OR(B2="auburn",B2="ashfield"),"innerwest", IF( ...

That will cut down on the number of IFs required.

[fil85]

That makes sense, great now my first problem is solved can i do the following

=IF(B2="ashfield",MEDIAN(IF('Ashfield 01'!D2:D1363="Ashfield",IF('Ashfield 01'!A2:A1363=0,'Ashfield 01'!F2:F1363,if(b2="manly",median(if('manly 01'!D2:D1363="manly",if('manly 01'! a2:a1363))))
Etc for around forty regions.

The aim of this is so i can type in a region in a cell and gain the median figure from data stored on up six work sheets per region.
Is there a better way of doing this

[MSP77079]

I will not pretend to understand what that formula was trying to do. But part of it I recognize as simply trying to figure out which worksheet to look at. For that, you should use the INDIRECT function.

For example, if I understand correctly, when cell B2 is "ashfield", you want to look on the worksheet named "Ashfield 1". Piece of cake. In place of
'Ashfield 01'!D2:D1363
you would use:
INDIRECT("'"&B2&" 01'!D2:D1363")
I do not think that upper case/lower case is going to matter. Try it.

There might be more to the logic that is not screaming out at me from what you have posted. My brain can only take in so much at one time. Sorry about that!

[fil85]

Thanks for the help sorry for the confusion

Basically what i need to do is when i type in ashfield in b2 I need it to search for all sheets with ashfield in the name.
Once this is done i need it to calculate the median for all prices that meet my criterea. In this case a unit number greater than 0 (located in column a) a district name of "ashfield" located column c. and finaly the median price column f, for all cells that have a unit number greater than 0 and are located in ashfield.

Cheers

[MSP77079]

when i type in ashfield in b2 I need it to search for all sheets with ashfield in the name

Hmmm ... not so easy in that case. There are two scenarios:
1. you have a fixed number of sheets and their names never change; in this case you can do it with nested if statements. But, you are likely to go over the limit of the number of nested if statements. So, another approach would be to use a formula which applies the criteria (look in this forum for examples of using SUMPRODUCT and/or Array Formulas to apply complex criteria). I am not sure if this can be done with SUMPRODUCT, or if you will need an Array Formula. It might help to see the workbook (a skeleton with a small amount of dummy data is good enough).

2. the number of sheets and/or names of sheets might change; in this case you will definitely need VBA.

Where it my workbook, I would go directly to VBA in either scenario. It will be very much simpler to write in VBA than with nested IF statements. And simpler to understand/maintain. And, if I start out in scenario 1, I am already set up to be able to handle scenario 2.

[MSP77079]

As a demonstration, using only a single worksheet, below is an Array Formula which returns the MEDIAN for a range where the entry in column A is >0 and the entry in column C equals whatever it is I typed in cell B2.

{=MEDIAN(IF(($C$14:$C$22=$B$2)*($A$14:$A$22>0)=1,$D$14:$D$22, ""))}

You do not enter the {}. Instead, you write (or paste) the formula without the {} and then, instead of pressing Enter, you press Ctrl+Shift+Enter.

What you would need to do is somehow make this work with multiple worksheets.

About the MEDIAN that you seek ... is it the MEDIAN froma single worksheet (i.e., there will only be one worksheet where all criteria are met), or the combined MEDIAN of all prices found in multiple worksheets (i.e., there will be more than one worksheet that contains cells meeting all criteria) ??

If the latter, the problem is much more complex because you need the union of ranges on multiple worksheets, right? In this case, I cannot see a solution that does not include VBA (but, maybe I have limited vision).

[fil85]

Yes it is for multible sheets, it think it is a bit out of my leage as I have no idea about VBA programing.

Cheers
Peter

[MSP77079]

Hi Peter,

I think what you want is not all that difficult. Can you upload a skeleton of your workbook with some dummy data in it? Maybe an example or two of what you expect for an answer?

I think a user-defined-function (UDF) could knock this out rather quickly. Yes, it does require some programming. But, I think that if you can handle nested IF statements, then with just a little bit of guidance you can handle this function in VBA.

[fil85]

Ok here is a zip with the book i have been working with, I have added into the cells what the optimum result is.

Thanks heaps for your help

If its a big task dont worry about it, ive allready told the Boss that it is out of my leage and no one else in the office can do it so im in the clear.

Cheers
Peter

[MSP77079]

Be sure to use Manage Attachments below, then Browse, then upload, then submit.

[fil85]

Tried to upload somthing that was to large.

Up now
Cheers

[MSP77079]

Got it. Back in a few.

[MSP77079]

does it really have to search over multiple sheets? Or does it only search over the sheet that matches the LGA type in cell B5 and the Year in cell B2?

If it does search over multiple sheets, then how do I use the Year?

[fil85]

Only has to search over the sheets named as such

[MSP77079]

Decided that (at least for now) it would be better to leave formulas in the cells. So, what the macro does is:
1) figure out which sheet to use based on LGA & year
2) figure out how many rows of data there are on that sheet
3) write formulas in cells B19 & B20 based on 1 & 2

Added a simple button to run the macro.

To see it ... press Alt+F8, this opens the Macro dialog. There is only one macro in there. Select it and press Edit. This will open the VB Editor with the macro's code window showing. You might need to resize the window to see it well. When satisfied close the VB Editor.

To test it, delete entries from B19 and or B20 and press the button. The data for 2004 do not work because they are all shifted by one column, right?

Questions??

[fil85]

That works great thanks heaps

Now two questions, first is there a quick way I merge about 200 files into the one workbook with two hundred sheets.

Secondly for a different task can I merge 5 workbooks onto on spreadsheet.

Cheers

[MSP77079]

I don't know the limit on the number of sheets you can have in a workbook. Wait ... OK, now I do. There is no limit except your machine's memory. See link:
http://office.microsoft.com/en-us/ex...992911033.aspx

To do this manually, with both workbooks open,
1. Select all sheets in the workbook to be copied, by selecting the first tab, holding the shift key and then selecting the last tab
2. Right-click on a tab
3. From the context menu, select "Move or Copy"
4. be sure to click the box at the bottom that says "Create a copy"
5. at the top, change the "To book" to be the one you want to copy to
6. I like the selection "move to end"
7. click OK

You will find yourself looking at the workbook you just copied TO. So, to close the other workbook, use Ctrl+Tab keys.

If you want a macro, here's one ...

Please Login or Register  to view this content.

It will loop through every open workbook, copy all worksheets to the workbook that has this code in it, then close the workbook it just made copies from and move on to the next.

You can paste this above or below the one in the workbook I sent. To make it easier to access, after you have pasted it to the VB Editor code sheet, go back to Excel, use Alt+F8 to open the Macro dialog. Select this macro from the list (there should now be 2 in the list). Select "Options". Click in the "Shortcut Key" box, hold down the Shift key and press a letter. Do not make it "Q", because I assigned that key to the first macro before I put a button in for it. How about "C" for "copy"?

Then to run the macro, just press Ctrl+Shift+C.

[fil85]

Thanks mate that works great.

Any chance of getting it to copy onto the one sheet, for a different puropse than what you have been helping me with.

Cheers
Pete

[fil85]

No dramas found one

[MSP77079]

Well ... I was working on it. Need it or not, here it is:

Please Login or Register  to view this content.

[fil85]

Is there any way of manipulating that macro so that we can search it over multiple sheets and bring back a median price. Eg if B2 = 2001 and B9= inner west then return a single median for Ashfield 01, canada bay 01, canterbury 01, leichardt 01, marrickville 01, strathfeild 01.

Cheers
Pete

[fil85]

Failing there being a solution I could just combine the sheets so i have a sheet for inner west 01 inner west 02 etc and use the same macro.

[MSP77079]

Sorry ... have been off-line most of today.

Is there any way of manipulating that macro so that we can search it over multiple sheets and bring back a median price. Eg if B2 = 2001 and B9= inner west then return a single median for Ashfield 01, canada bay 01, canterbury 01, leichardt 01, marrickville 01, strathfeild 01.

I assume you are referring to "original" question, and not the two later questions about copying worksheets to a single workbook and copying multiple workbooks/multiple sheets to a single workbook/worksheet, right?

If so, then yes. What I would suggest is:
1. user enters the year (say, 2001)
2. macro locates all worksheets that are named "**** 01"
3. for each worksheet found in #2, puts the LGA reference and the two formulas for that LGA reference, maybe skips a row and moves on to the next worksheet that matched.

If you want them sorted in alphabetical order, that is also possible. Would change how the macro works slightly, but not significantly more difficult.

Is this what you had in mind?

[fil85]

I belive this is what im after, basically the macro you established works great.
What im trying to get to now is a single median for the region that the lga is in. So a median for a maximum of 6 lgas.

Cheers
Pete

[MSP77079]

Got it. That is different from what I wrote, but closer to what I THOUGHT the question was a couple of days ago. So, let me restate ...

Keep what you have now ...
1. user supplies both year AND LGA.
2. return the 2 formulas as we do now.
IN ADDITION ...
3. when we've found the sheet to use for part 2, above, read from that sheet what region the LGA is in
4. search all sheets for the same year and build an array (internal to memory) containing all LGA's in that region and the prices associated with each property.
5. from that array, determine the median, and maybe some other statistics, like number of LGA, number of properties.

Is that about it? Both formulas? In other words, a formula that includes column A not 0 and another formula including Column A zero or not?

If this is it, I think I can knock it out this evening. Will need to fake some data since I do not have a workbook for more than a single LGA right now.

[fil85]

Mate that would be great, only do it if you have time though.

Hopefully this one i can understand and adapt for the last critera.

Cheers
Pete

[MSP77079]

Just to be sure I understand ...
LGA (or District) = Liverpool; Region = South West

So, for Liverpool, return Median for Liverpool and also Median for South West, which is AT LEAST Liverpool and Campelltown (possibly others to be added later).

If this is correct, then ...
Will all Districts and Regions be listed on Optimum sheet? If not, how do I determine the Region?

[MSP77079]

If these data are representative, there seems to be data for only one district per worksheet. So, a lot of the IF statements we are using seem unnecessary. Once we have found the sheet for a district, the only IF we need is for the LGA Median Unit Price (If column A is > 0).

Is this right?

[fil85]

What i was planing to do was on the optimum sheet have one cell b2 that you type in the lga.
Then use an if statement to work out the region displayed in cell b5. =if(b2="ashfield","inner west",if(b2="mosman","Lower north shore","")

Cheers
Pete

[MSP77079]

OK. Sorry ... I got a different idea from the workbook.

You might want to look at what I have done so far (not much and easily undone) before I get too much further along.

[fil85]

Thats pretty much what im after but only for one lga at a time.

So I enter the lga and year in a cell and in return get median prices for houses and units for the lga and the region.

Cheers

[MSP77079]

Sorry ... I finished this before I saw your note. I'll change it so it only works one LGA (District?) at a time.

Not sure you answered all of my questions from the 2 postings 1.5 hours ago. I am working as if all of my assumptions there were correct (which greatly simplifies the equations). So, if those assumptions are NOT correct, will need to re-work this. I left the older code (more complex, and frankly I think redundant in parts), so no great pain to go back to those. I just don't think they are necessary.

Instead of IF statements for getting all Districts in a Region, I really think you should have a table of these. That is what I will implement in next revision.

[fil85]

What your doing is great mate.

Sorry about the question two up didnt see it before I replied, yes your assumption is correct we can get rid of the name search. I originaly thought we would only use a couple of sheets and we would need to search for the name.

A list of regions and lgas is also fine.

Cheers
Pete

[MSP77079]

Glad you like it. Now all we need to do is add the Ring?

[fil85]

Yer just the ring and a total median price list for all units and houses in sydney ie all work sheets for a year.

If your to busy let me know and i will atempt the vba by myself.

If not here is the workbook with updated region and ring list.

[MSP77079]

Sorry I did not respond immediately. I had another project I had promised to finish yesterday (PowerPoint), so I did come back to this forum until just now. I can definitely work on the Ring and the entire database (for a given year, I assume).

It will probably be ~12 hours from now before I get a chance to look at it again. So, if you make progress in the meanwhile, let me know. If you get started and get stuck, post it here and I'll see if I can find the problem.

[MSP77079]

I lied. Finished my first project this morning more quickly than I imagined, and no one was ready to start on the next one yet. So, I worked on your project instead.

Had to change the criteria slightly for matching worksheet names. I allowed too much "wiggle room" in the earlier versions, and so it could not differentiate North Syndey from Syndey. Fixed that and also added Debug prints (which you can only view from VB Editor; View >> Immediate Window) so that it lets us know which worksheets it found for matches. This will come in handy if you ever get unexpected results.

By the way, I failed to note it before, but I added some pretty detailed error handling in the previous version (4), including a long-forgotten VB feature ... line numbers! So, if ever an error occurs, it will at least let you know which section of the code is the problem.

One thing I would do before calling this finished, if it were my project. We now have 3 sections in here with nearly identical code (and the original version has also very similar code); namely, where it searches for matches by Region, Ring, and Town, then puts the formulas together. I would take that code and make it a subroutine. Doing that makes it more difficult to understand the first time through, but much easier to maintain in the long run.

Why? Take for example the change I found was needed to differentiate Syndey from North Syndey. That identical logic was in the routine in 4 places, so I had to find and make the exact same fix in 4 places. Putting the code that is identical logic into a subroutine would mean there was need to that problem only once.

If I have time later today, I'll do that just so you can see what it would look like. Meanwhile the attached version should be easier to understand the first time through.

[fil85]

HI after i loaded in all the properties i started to have a few problems, one being unable to set the formula array property for the range class.

After Line 600.

[fil85]

Would it make any differencr that the sheets imported were in csv format prior to import?

[MSP77079]

Coming in from CSV files would make no difference, as long as things end up in the correct columns and that column F contains at least a few numbers.

And even in the worse case, if column somehow was converted to all text, I am not able to create this error using version 4.2.

Getting to line 600 means that it should be returning a formula to cells B9 & B10, and the problem occurred when it started working on the Region. Do the results in B9 & B10 look reasonable?

Next thing to look at, in the VB Editor use View Immediate Window, if you have run the routine multiple times, scroll to the bottom. You should see something that looks like the following:

Please Login or Register  to view this content.

This will tell you which sheets (if any) it found to use for the Region calculation.

If looking at those two things do not provide any clues, the next step is to add another debug.print to the code. Go to the VB Editor, and just above line number 650 you will see this code:

Please Login or Register  to view this content.

Change it by adding these 2 lines:

Please Login or Register  to view this content.

After adding those lines, run the routine again and see what shows up in the Immediate Window.

Meanwhile, I have an enhanced version I am excited to tell you all about. But, I think you will be more enthusiastic about it after we fix this problem.

[fil85]

I have no idea about this but when i run a search for ryde it will show the ring which its searching but not return a result. IE will return all the lga's in the ring however wont calculate the median.


============= Matches for lane cove=============
=== District ===
Lane Cove 03
=== Region: Lower North Shore===
Hunters Hill 03
Mosman 03
North Sydney 03
Willoughby 03
=MEDIAN(IF('Lane Cove 03'!R2C1:R1046C1>0,'Lane Cove 03'!R2C6:R1046C6),(IF('Hunters Hill 03'!R2C1:R430C1>0,'Hunters Hill 03'!R2C6:R430C6)),(IF('Mosman 03'!R2C1:R984C1>0,'Mosman 03'!R2C6:R984C6)),(IF('North Sydney 03'!R2C1:R2924C1>0,'North Sydney 03'!R2C6:R2924C6)),(IF('Willoughby 03'!R2C1:R2172C1>0,'Willoughby 03'!R2C6:R2172C6)))
=MEDIAN(IF('Lane Cove 03'!R2C1:R1046C1=0,'Lane Cove 03'!R2C6:R1046C6),

Cheers Peter

[MSP77079]

If that is exactly what is in the Immediate Window (and you did not miss something further below), then this is indeed odd. The formula it gives for the Unit price appears correct, and it should give an answer in cell B15. The formula it gives for House price appears truncated and is an obvious problem.

On the other hand, if what you are telling me is that both equations appear correct in the cells, but refuse to give results, that is a horse of a different color. I would need to start looking at what the limitations are for the MEDIAN function. It's possible that it cannot handle more than a certain number of inputs before going crazy.

I will explore the MEDIAN function limits. You should confirm whether you have posted everything that was in the Immediate Window or not.

[MSP77079]

The Median function should be able to handle a maximum of 30 arrays. This example is not close to that limit.

On the other hand, I have a feeling that when you have all of the data in the database, it might be a different story when doing the comparison for the entire town. Hmmm ... might be time to start considering a Plan B.

[fil85]

I closed the workbook and restarted excel/vba editor to make sure and it is deffinatley the only thing being shown in the intermediate editor.

Also when I type in Penrith and 2001 it is saying no such sheet exists however this is not the case.

Cheers
Pete

[MSP77079]

The good news is ... I have re-created the problem. I removed the "matching year" requirement for the portion that does the town calculation as a test to see what would happen as the number of matching arrays grows.

The other news is that it hit the wall at a paltry 6 sheets matching.

[fil85]

Ahh I see I take it this means we are unable to achive this task.

Ohh Well
thanks for the help
Cheers
Pete

[MSP77079]

Not at all. It means were are back where we were 2 days ago. I need to decide whether to:
1. collect all of the "matching" prices into an array in memory; or,
2. write all of the "matching" prices into hidden worksheets in the workbook

I have tested Median with adjacent columns in a workbook (just now) and it has no problem at all with many columns of numbers stretching from row1 to row 65000+. So, I am favoring the latter approach. The routine will likely run a tad slower than before.

I'm not ready to give up unless you are.

[fil85]

Yer mate continue on if you belive it can be achived

Cheers
pete

[MSP77079]

Pete,

Absolutely. Here's what I have in mind ...
Ditch the in-cell formulas completely (unfortunately) except for the single District, maybe. We will have 2 hidden sheets. One for Units, one for Houses. Start with both sheets blank. Find the correct data sheets (as now). Read columns A & F on each of these (this is not as slow as you think, VBA can read a column of 50,000 numbers in the blink of an eye). If Column A is blank or 0, write the value in column F to the hidden sheet for Houses, othewise, write it the Units sheet. If we run out of rows in the first column of a hidden sheet, move to the next column and continue. The writing to the sheets is what will take some time. But, if we keep the sheets hidden, there is nothing for the screen to update, so it should not be too bad. We'll see. Maybe a few seconds, I figure.

No matter how many sheets you add, it is very unlikely we will ever fill 30 columns. So, we can still use the MEDIAN function. It is no longer in an Array (no IFs => no array formula). Should work swimmingly.

What I suspect is throwing the sand in the gears is that Array formulas require a lot of Excel memory over-head. If we boot the Array formulas, we should have no problems.

Return only the net result to the Optimum sheet. Clear the 2 hidden sheets when finished.

The only other option that I thought of in the meanwhile (while eating dinner), was to use the weighted-average of the medians as an approximation. Not being all that good at Statistics, I have no idea if that is a reasonable approximation or not. But, it should be a lot better than a simple average. You still won't have your odd million-aire home messing up the numbers too badly.

I'll get to work on the hidden sheet plan. You can mull over the average of the medians. Call it Plan C. But, Plan B is gonna work!

Back in a few.

- Pete (aka MSP)

[MSP77079]

Here it is ... I will follow-up with a posting that explains the features.

[MSP77079]

First, I should explain that I built this from the enhanced version I mentioned a few hours ago. So, it already had busted up the one big macro into smaller, reusable parts. I don’t normally do flow diagrams, but if you want a blow-by-blow of who calls whom when I can put that together.

Let’s start with this … there are 3 very hidden sheets. If you are not familiar with “very” hidden sheets in Excel … let me explain. There are the sheets you see (visible, duh!). There are hidden sheets that you can hide (or unhide) using Format >> Sheet >> Hide (or Unhide); those are called “hidden”. But, there is a third state called “very hidden”. There is nothing a person can do from the normal, Excel interface to unhide a very hidden sheet. To do that, they need to go to the VB Editor and either change the sheet’s visible property manually or use a line of VB code.

If you want to see them, I have put a (not hidden) macro in the workbook to do that. You can press Ctrl+Shift+U (unhide!) or use the Alt+F8 macro dialog.

The 3 very hidden sheets are:
1. Sh333 (code name = List); this maintains a UNIQUE list of LGAs/Districts … more on that later
2. Sh444 (code name = House); this is normally empty; is used to keep a list of prices for houses
3. Sh555 (code name = Units); this is normally empty; is used to keep a list of prices for Units

There is a Workbook_Open macro (written on the code window of the “ThisWorkbook” object). Whenever the workbook opens (with macros enabled), it will run two routines:
1. maintainListOfDistricts
2. clearHiddenSheets
Each of which does pretty much what their names imply. More about item #1 to come.

On this same code window you will also find a Workbook_BeforeClose macro, which runs just before the workbook closes to once and finally be sure that the very hidden sheets for Units and Houses are empty.

There is also a Worksheet_Change macro; this is written on the code window of the “Optimum” sheet. Whenever a change is made to any cell on this worksheet, this macro runs. But, it doesn’t do anything unless the cell changed is B5 (LGA selection) or in column G (a district added, deleted, or name modified).

What happens when someone makes a change to cell B5? As you see in the workbook, there are formulas in column C that state LGA, Region, Ring, and Town for the selection in cell B5. If that cell value changes, we don’t want people thinking that the numbers below reflect that change. So, the code clears the numbers below.

If you have not noticed already, there is a drop-down list in cell B5 so that folks will be encouraged to select correct spellings for LGAs. The purpose of the “maintainListOfDistricts” routine is to keep this list up-to-date. The Workbook-Open routine makes the update run every time the workbook is opened. And the Worksheet-Change routine makes the update run any time an entry is altered in Column G.

The formulas in column C are re-written every time that the main routine is run. The formulas should alert you BEFORE you run the main routine if there is a problem with the Master table (i.e., Excel cannot find a District that does not match the LGA). Of course, this should never happen since the user should never be able to put anything in cell B5 that is not on the list, and the list is constantly maintained.

If you have any questions … holler.

[fil85]

Thats great mate does every thing we want it to do

Cheers
Thanks For all your help

[fil85]

Hi mate i have been using this macro on the sheets quite sucessfully for the past few months, and i have thought of a few ways we could improve the workbook.

Mainly i would like to put a year range in instead of a single year so i can note the changes in the prices over the years. So instead of one set of outcomes we have a set of 6 outcomes.

The other is mearly limmiting the price range that we callculate the median for. After reviewing the data it seems that in some years there are anougth outlying figures to effect the median price.
So if its possible have a new field were we could stipulate the min price and max price that it calculates the median for.

Once again only if you have time as this is not urgent at all.

Hope all is well
Cheers
Pete