Microsoft Excel Q&A

[CanOz]

Well if it's something simple, I can have a look. No payment needed.

If it's something you feel very confident about in terms of profitability, try to break it into chunks so that whomever you get to help you, you're not giving away the farm. If you know what I mean? Edges are hard to come by, so you can even get different people to code up different aspects, then combine them yourself.

Its mostly seasonality and some range analysis, nothing ground breaking, just handy stuff to have in the back of your head...thanks for the offer. I'm good now

 

[Gringotts Bank]

I'd like to get Excel 2016 as a standalone product. I don't want to subscribe or use Excel Online, and I don't want the whole Office suite. Just a recent version of Excel which is transferable when I get a new PC.

The MS website doesn't make it clear what the license allows you to do. So if someone could advise me please, that would be hlepful.

 

[PZ99]

I downloaded free Kingsoft spreadsheets some time back which is easy to use.

Not sure what it's like now.

http://www.kingsoftstore.com/spreadsheets-free.html

 

[Gringotts Bank]

I downloaded free Kingsoft spreadsheets some time back which is easy to use.

Not sure what it's like now.

http://www.kingsoftstore.com/spreadsheets-free.html

Thanks - I have something similar in gnumeric. But I definitely need Excel.

 

[kid hustlr]

I have time series data (1m) for a security. I want to know what time of day the highs and lows occur in said stock.

how can identify the high and low for each day so i can then filter for all HOD's or LOD's?

Woudl be some kind of 'max if' type formula but it's trickier than that I think.

 

[ajcode]

I have time series data (1m) for a security. I want to know what time of day the highs and lows occur in said stock.

how can identify the high and low for each day so i can then filter for all HOD's or LOD's?

Woudl be some kind of 'max if' type formula but it's trickier than that I think.

View attachment 70480

Hi kid hustlr,

Interesting problem. To calculate the high/low of the day, we need to know the start and end row of minute data for a particular date, then we can calculate the max/min between that row range to get the high/low for that particular day. I have added 2 columns named DateStartRef which is where the Data of that day start and DateCount which counts the number of times a particular date appears. Here is the spreadsheet in formula display:



If you want to know what time the High/Low of the day occurs, you can simply do something like if(K3=G3,E3,0) for high and if(L3=H3,E3,0) for low and fill down.

Is this sort of what you're after?

I have attached the spreadsheet HiLoMinuteData_20170325.xls.

Cheers,

Andrew.

 

[DrBourse]

I was wondering if anyone would be able to assist me with an Excel Formula that I just cannot fathom, maybe it's just so simple that I can not see it.….

For Example – If My excel spreadsheet column has 10 cells with amounts in the first 5 cells ($2, $4, $6, $8, $10) – I do not require the normal total of $30 in Cell A11 - how do I ask Excel to use the LAST amount in the column, in this case, in Cell A5 ($10) as the total for Cell A11….

There may be other columns that I will use some of which will have more cells (abt 300) – Same scenario, how do I get the last amount (in whatever cell it may be) to be entered to automatically in the Total Cell....


Don't waste too much time on this, given time I will work it out - but it's just "pizzing me Orf" atm, so I thought I would ask for some help...

Thank you (in anticipation)
Cheers..
DrB.

 

[wabbit]

I was wondering if anyone would be able to assist me with an Excel Formula that I just cannot fathom, maybe it's just so simple that I can not see it.….

For Example – If My excel spreadsheet column has 10 cells with amounts in the first 5 cells ($2, $4, $6, $8, $10) – I do not require the normal total of $30 in Cell A11 - how do I ask Excel to use the LAST amount in the column, in this case, in Cell A5 ($10) as the total for Cell A11….

There may be other columns that I will use some of which will have more cells (abt 300) – Same scenario, how do I get the last amount (in whatever cell it may be) to be entered to automatically in the Total Cell....
View attachment 180504

Don't waste too much time on this, given time I will work it out - but it's just "pizzing me Orf" atm, so I thought I would ask for some help...

Thank you (in anticipation)
Cheers..
DrB.

 

[DrBourse]

View attachment 180518

Thanks wabbit - much appreciated - will follow your advice....

 

[DrBourse]

View attachment 180518

Thanks again @wabbit ....
That worked perfectly - I have calmed down thanks to you - Not sure exactly how, but If you ever feel I can help you in any way, please let me know....
Cheers M8....
DrB.