I cannot understand why a cell B1 returns 0 when I refresh the sheet.
I get a daily share price from the internet. That share price is in cell a1. I track the share price on a daily basis, however, I want to record its highest price throughout the year. the formula i use is
=MAX(A1:B1)
However, if A1 has £6.45 when I refresh B1 will only return 0. I've checked the formatting of each cell, I even tried using the following: =IF($Y$1,0,MAX(A1:B1)) with 0 in Y1
I'm baffled
Thank you
Dave
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Administrative Note:
Is your forum profile showing the Excel PRODUCT that you need this to work for?
Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.
The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.
Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Is the £6.45 TEXT or a real NUMBER? Sounds like it might be the former, which is why we need to see the workbook to confirm.
Did you put the formula in cell B1? If so, =MAX(A1:B1) will create a circular reference error and always return 0.
If you manually typed the pound sign(s) ( £ ), that will make the cell text.
Remove any manually entered currency symbols and use Cell Formatting | Currency.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
could try
Formula:
Please Login or Register to view this content.
the £6.45 is from the internet (share price). can you tell me how I can attach a sanitised spreadsheet, I can't seem to find the attached symbol?
dave
Read post #2.
Chances are it is being seen as text and/or it has non-printable character before and/or after it.the £6.45 is from the internet (share price)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks