| Pages: [1] :: one page |
|
|
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
SpaceSlag
Gallente
the united
Negative Ten.
   |
Posted - 2009.04.02 17:13:00 -
[1]
I'm creating a spreadsheet that will determine profitability for the macro economic industrialist out there for a myriad of market items.
I am having trouble with my auto-update feature. I plan for it to poll data from Eve-Central from a very recent time interval so industrialists can get a relatively accurate showing of the market.
The spreadsheet is designed to refresh manually or at open of the document so that it doesn't spam the site and force their bandwidth through the roof.
Well, when you pull data from the eve market, everyone knows that one day there can be 10 buy/sell orders for a particular item, and the next day there can be 50buy/sell orders for that same item. THIS is creating issues.
I have all my formulas set, except for the SUMPRODUCT formula.
I'm using the following formula: =((AVERAGEIF(B3:B200,"<" & G5,B3:B200))+(SUMPRODUCT(A3:A200,B3:B200)/SUMIF(B3:B200,"<" & G5,A3:A200)))/2
"It's the average between the set average and the set weighted average."
G5= B3*1.3333
Basically it's designed to kick out numbers that are 33.33% higher than the lowest price. I selected that range for the volume found within that set range without the deviation just getting completely out of hand.
So, I tried using the COUNT command to automatically display how many lines are used in the SUM & AVERAGE commands.
Finally, here's the question: How do you use another cell's data as part of a range for another cell's function?
i.e. =SUM(A1:A(1+F5)
Whereas, F5 is the COUNT function described above.
Become a pirate without fear of death!
|
Kazzac Elentria
|
Posted - 2009.04.02 17:54:00 -
[2]
Actually getting your info from Eve Metrics might work out better.
Their information tends to be more up to date and has less outliers. |
Drab Cane
Mining Emporium inc.
|
Posted - 2009.04.02 18:39:00 -
[3]
Normally I'd use VBA to modify a function dynamically, based on another cell's value.
Another route is to set up a second range of cells, somewhere where it's hidden out of sight (different sheet, whatever), that will conditionally "echo" the values in your sum range.
For example (using your example cell references), the formula in the first cell of the hidden range would be:
=IF(ROW(A1)-ROW($A$1) + 1 > $F$5,0,A1)
This assumes that A1 is the first cell in your sum range, and F5 is the cell with the total count.
Copy the above formula to the rest of the cells in your hidden range, and make sure that the hidden range is long enough to accommodate all the possible cells in your sum range.
The hidden range will 'echo' only the top values of the orignal sum range, and fill the rest of the hidden range with zero values.
Calculate the sum on the hidden range, and you're done.
This trick can be very useful whenever you have to conditionally include a cell in a sum range.
Note: This thread probably belongs in the Development forum 
-----------------------------------------------
- Who Dares, Wins
|
SpaceSlag
Gallente
the united
Negative Ten.
|
Posted - 2009.04.02 20:55:00 -
[4]
Maybe not. The spreadsheet will make quite a discussion piece and I hope it will revolutionize the market (driving prices lower).
I made a count of the rows that were less than the 33.33% variance and then used that count as a reference in offset function made part of the sumproduct formula.
Bottom line: It works! Thanks for turning me onto the different way of thinking. Now it's time for me to refine the sheet and make it presentable. This should take a couple days, but I plan to have version 1.0 out soon.
Become a pirate without fear of death!
|
Drab Cane
Mining Emporium inc.
|
Posted - 2009.04.02 21:59:00 -
[5]
Cool, I learned something new as well.
My God, how long has that function been there . . .
-----------------------------------------------
- Who Dares, Wins
|
|
|
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |