MODE() Array formula.

[Nacky]

Hi, I have the following array formula:


[pre] {=IF($BK30=0,0,IF(ISERROR(MODE(IF('LASTPRICE!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000))),AVERAGE(IF('LASTPRICE'!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000)),MODE(IF('LASTPRICE'!$C$1:$C$50000=$BF30,'LASTPRICE'!$G$1:$G$50000))))} [/pre]


What it does:

Searches for the most common price in a data sheet. If a common price is not found (or if only 1 product with 1 price is found) it will give the average instead. (Mode will return an error if only 1 price or no common prices are found). So the average is there to avoid the #N/A.


Is there a simpler way to write a similar formula. My spreadsheet has hundreds of products so there are hundreds of iterations of this array formula and it takes a while to calculate.


Regards,

Nacky

 

[SirJB7]

Hi, Nacky!


Searching thru 50K cells to find out any value -mode in this case- is a hard time consuming operation.


I have several doubts that you may clarify:

a) how many rows do you have in range where BK30 belongs?

b) the data in LASTPRICE columns C and G, how is it updated/entered?

c) do the 50K cells have values or just have formulas for growing in the future?

d) how many different products do you have in those 50K cells?


It would be advisable if you can upload a sample file. If you don't know how, check the third of four green sticky posts at this forums main page.


Regards!

 

[Nacky]

Hi SirJB7,


The Data sheet pulls from a database. The database information I coded in VBA and pulled via a simple .copyfromrecordset command. To expand and answer your questions:


- Data rows can be from anywhere between 100 and 50k (Depending on Range of Days requested by user input).

- Number of products are about 15.

- The data is updated dynamically via a user input of a date range. (example: Give me prices from Feb 1,2012 to Feb 7, 2012)

- The LASTPRICE Sheet has no formulas, only text/number formated cells of data.

- Value in $BF30 contains a *KEY* Value (Concatenation of CityName+ProductName)

- $BK30 is just a COUNTIF() to count the number of ProductNames in a given CityName.


As for a sample file.. I'll see if I can make one. The spreadsheet I have has far too much confidential information to breakdown into a simple sample file

 

[SirJB7]

Hi, Nacky!

I understand that, use the RANDBETWEEN and CHOICE functions to randomize data.

My idea is trying to know the data ranges, values and quantities, for example the 50K records maybe summarized in a lesser number, I don't know. That's where the sample file will help.

Regards!

 

[Nacky]

I can't upload the sample file to a sharing website from work. I'll email it to myself and do it once I get home..


Notice: Sample File will have Automatic Calculations off. Data sheet has 50k rows of Data (1 months Data, Randomized as suggested)