Microsoft Excel Q&A

[Naked shorts]

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]

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]

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]

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

 

[Gillie]

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]

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]

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]

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]

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]

=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]

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]

What about,

Paste Special > Paste unformatted text.

 

[nulla nulla]

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]

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]

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]

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]

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

 

[James58209]

=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]

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]

=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",""))