Microsoft Excel Q&A

Naked shorts · 14 September 2009

So I'm having a bit of trouble with excel, a data point in my graph screws up when I change the referenced cell to one that has a formula in it. Any ideas why it the excel chart looks at the formula in the cell instead of the value of the cell? I have spent have the day trying to figure this out

peterh · 14 September 2009

I'm not sure but using the formula may have changed the cell to a text field when Excel is looking for a date field. Converting it to a date field might help. Perhaps using the Date(year,month,day) function. Just guessing though as I couldn't quite work out your spreadsheet with the isolated data point.

Uncle Festivus · 6 October 2009

I am trying to create a small chart with a stocks low last and high price, from 3 cells, in a horizontal bar sort of thing, like the pic below. Any ideas?

Seems like a simple request, although I can't find anything on the net that will do it (simply?), or maybe I am just missing something?

pj2105 · 6 October 2009

Best formula in excel is vlookup, I've used it a million times.

Gillie · 6 October 2009

Naked shorts said:
So I'm having a bit of trouble with excel, a data point in my graph screws up when I change the referenced cell to one that has a formula in it. Any ideas why it the excel chart looks at the formula in the cell instead of the value of the cell? I have spent have the day trying to figure this out

Ok Naked I think I see what the issue is.

You are converting a date from american (09/11/09 - excel number 40067) to australian/english (11/09/09 - excel number 40126). In excel the date is actually stored as a number (as seen above) not in the afore mentioned format, so when you are trying to disect the days and months you are in effect splitting this number - that is why the dates are not calculating correctly.

Instead of disecting the date into its various components, right click on the cell and select format cell go into the date section and ensure that it is on English - Australia, you may then have to redo your cell allocations for your calculations that tie into your chart.

Hope this helps

Gillie · 6 October 2009

Uncle Festivus said:
I am trying to create a small chart with a stocks low last and high price, from 3 cells, in a horizontal bar sort of thing, like the pic below. Any ideas?

Seems like a simple request, although I can't find anything on the net that will do it (simply?), or maybe I am just missing something?

This type of chart can only be done with a VB macro in excel. Quite simple done this type of chart several times. The code can be found at the following website and you can modify it for your needs.

http://excel.tips.net/

Uncle Festivus · 6 October 2009

Gillie said:
This type of chart can only be done with a VB macro in excel. Quite simple done this type of chart several times. The code can be found at the following website and you can modify it for your needs.

http://excel.tips.net/

Thanks, but what am I looking for, as there is a lot of stuff there? Does this type of chart have a name?

Trembling Hand · 6 February 2010

Hi gurus.

if I wanted to compare two cell values as the same value I use something like this

=IF(A1=A2, "TRUE", "FALSE")

What about if I wanted to compare two values as true + or - 0.01% or if the values where very close but not exactly, something like 5678.67 and 5679.08, like less that 1 point diff?

Any ideas?

skyQuake · 6 February 2010

Trembling Hand said:
Hi gurus.

if I wanted to compare two cell values as the same value I use something like this

=IF(A1=A2, "TRUE", "FALSE")

What about if I wanted to compare two values as true + or - 0.01% or if the values where very close but not exactly, something like 5678.67 and 5679.08, like less that 1 point diff?

Any ideas?

=IF(abs(A1-A2)<1,"TRUE","FALSE")
For a tick

=IF(abs(A1/A2-1)<0.1%,"TRUE","FALSE")
For 0.1%

Looks like you're dealing with annoying data

Trembling Hand · 6 February 2010

skyQuake said:
=IF(abs(A1-A2)<1,"TRUE","FALSE")
For a tick

=IF(abs(A1/A2-1)<0.1%,"TRUE","FALSE")
For 0.1%

Looks like you're dealing with annoying data

Thanks that the stuff

Trying to make sense of ASX data. hint open close index gaps

nulla nulla · 6 February 2010

I have a problem with the version of Microsoft Excel installed on my laptop. At the end of each day I cut and paste the details from my comsec share portfolio list.

In all my other computers running the same version of Excell I am able to convert the data (text) in the columns "Market Value" & "Profit/Loss" to a number by:
1. highlighting the columns at the top of the spread sheet;
2. selecting "format" through either the task bar "edit" option or by right clicking the mouse;
3. Selecting "number" in the format options;
4. Then selecting the appropriate number of decimal points I want to expand the data to.

The numbers then convert from the text format "$1900" to the number format "1900.00" and I am able to copy and paste out the contents for seperate calculations to my hearts desire.

In this laptop the data remains unchanged and cannot be totaled as a column of numbers or any thing else. I have found means per google of double handling the data to get it into a manipulatable number but I want to do it without all the double handling.

Can anyone assist me in identifying why this laptop treats the data differently and what I need to change to get the same performance? I have already upgraded, removed and reinstalled to no avail. Thanks in advance.

Trembling Hand · 6 February 2010

What about,

Paste Special > Paste unformatted text.

nulla nulla · 13 February 2010

Thanks for the response. I had previusly googled the problem and identified approximately 6 different ways of highlighting, copying and special pasting of the data columns after copy and pasting them from the original Comsec list.

I was hoping to identify the posible difference in the computer set up between the laptop, that doesn't allow formating, and the other pc's/laptops that do allow formating.

I'm going to hunt for any upgrades now from microsoft that might address this issue. Only other thing I can think of is it may be a firewall / macro restriction / ActiveX problem.

lbarks · 13 February 2010

Hi
this may seem a bit basic but im currently trying to set up a portfolio spreadsheet in excel. im after the latest stock price, %change, volume ?
I have been able to get the price through google spreadsheets which i found the code in this thread, but am looking to do it all in excel.
any help would be great, thank you

Trembling Hand · 6 April 2010

Gurus, nerds & nutters here is another one I'm stumped on.

Trying to check if the last cell is the highest or lowest for a range. Easy if looking for just the Highest,

=IF(A5=Max(A1:A5), "High")

But how do I get,

=(A5=Max(A1:A5), "High") OR IF(A5=Min(A1:A5), "Low")

rryall · 6 April 2010

Trembling Hand said:
Gurus, nerds & nutters here is another one I'm stumped on.

Trying to check if the last cell is the highest or lowest for a range. Easy if looking for just the Highest,

=IF(A5=Max(A1:A5), "High")

But how do I get,

=(A5=Max(A1:A5), "High") OR IF(A5=Min(A1:A5), "Low")

=IF(A5=MIN(A1:A5),"MIN",IF(A5=MAX(A1:A5),"MAX"))

Trembling Hand · 6 April 2010

Thanks for that. Always make sense after you are shown.

James58209 · 6 April 2010

rryall said:
=IF(A5=MIN(A1:A5),"MIN",IF(A5=MAX(A1:A5),"MAX"))

You could add an empty string as the "else "case of the second IF formula:

=IF(A5=MIN(A$1:A$5),"Low",IF(A5=MAX(A$1:A$5),"High",""))

Otherwise you'll get FALSE appearing in the rows which are neither highs nor lows.

And of course you'll need $ to avoid the A1:A5 changing to A2:A6 etc. when you fill down.

leedskalnin · 6 April 2010

Trembling Hand said:
Gurus, nerds & nutters here is another one I'm stumped on.

Trying to check if the last cell is the highest or lowest for a range. Easy if looking for just the Highest,

=IF(A5=Max(A1:A5), "High")

But how do I get,

=(A5=Max(A1:A5), "High") OR IF(A5=Min(A1:A5), "Low")

=IF(A5>LARGE(A1:A4,1),"High",IF(A5<SMALL(A1:A4,1),"Low","Equal"))

leedskalnin · 6 April 2010

leedskalnin said:
=IF(A5>LARGE(A1:A4,1),"High",IF(A5<SMALL(A1:A4,1),"Low","Equal"))

sorry last quotation should be "" not "Equal"
=IF(A5>LARGE(A1:A4,1),"High",IF(A5<SMALL(A1:A4,1),"Low",""))

Microsoft Excel Q&A

Naked shorts

Attachments

peterh

Uncle Festivus

Attachments

pj2105

Gillie

Gillie

Uncle Festivus

Trembling Hand

Can be found on the bid

skyQuake

Trembling Hand

Can be found on the bid

nulla nulla

Positive Expectancy

Trembling Hand

Can be found on the bid

nulla nulla

Positive Expectancy

lbarks

Trembling Hand

Can be found on the bid

rryall

Trembling Hand

Can be found on the bid

James58209

leedskalnin

leedskalnin

Similar threads

Microsoft Excel Q&A

Attachments

Attachments

Can be found on the bid

Can be found on the bid

Positive Expectancy

Can be found on the bid

Positive Expectancy

Can be found on the bid

Can be found on the bid

Similar threads

Privacy & Transparency

Privacy & Transparency