Why won't a simple formula work in an empty cell
I am a newbe to excel but I have a reasonable understanding of formulas, but the simple formula has me baffled!
Cells
A1 =£1.27
B1 =Empty (no values)
Formula in B1
=if(A1>B1,A1,B1)
The outcome in B1 = 0 (format General and Currency £0.00)
This has baffled me so I populated Cell C1 with £1.50 and changed the formula to if(A1>C1,A1,C1) and it worked.
What do I have to add anything to my original formula to make it work? Any advice will be greatly appreciated.
Welcome to the forum.
Try this:
=if(OR(A1>B1,B1=""),A1,B1)
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.
That's strange, I get 1.27 in B1 in those circumstances.
You have of course created a circular reference by specifying B1 in a formula in B1 but it's not clear to me why that would make any difference.
Are you able to upload your workbook.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thank you AliGW and Richard, unfortunately Ali your formula still produced a 0, I have attached my spreadsheet to see i what I am doing wrong!
1. How did you get that "disclaimer" message at the top?
2. Did you notice, as Richard pointed out, that you have a circular ref error in B1 (caused when a cell references itself), circ ref errors can cause all sorts of unexpected problems.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The disclaimer is purely for accessing market data via excel, and is a standard disclaimer so that they cannot be held accountable for the information provided. With regards to circular ref error in B1, as I stated I am a newbe to excel and will gratefully receive advice on how to resolve a circular ref error.
You'll need to enable iterative calculation in the Excel Options (or Preferences if you're on a Mac).
Rory
Many thanks Rorya will do on my mac
Sorry, I was asking how it was applied. Im not really bothered about what it does or what it's for.Originally Posted by easygoingdave
A circ ref error is when a cell refers to itself in a formula. In your case, you have a formula IN b1 that refers TO B1
Hi, yes I now realise I have circular error, but I am not sure how to change the formula to reflect what I want achieve. All I want to do is: if the data (number) in A1 is more than the data (number) in B2, then replace data in B2 with A1 data. however if it's not then keep the data in B1. I thought it was a simple problem, but because B1 is initially empty the formula does not work! Any advice would be greatly appreciated.
If you turn iteration on, your formula should work.
Hi all!
1. What does Option "Enable Iterative Calculation" actually means?
2. And What are other options for under "Enable Iterative Calculation" i.e. "Maximum Iterations" & "Maximum Change".
3. In which situations this option can be turned on or off?
Explanation of above points will be very helpful for those who have no idea about this option.
Thanks
Hi Rorya
Although I don't understand iterations, it worked!!!! Thank you, much appreciated
In that case, and because it's not generally a good idea to build in circular references (and believe me if circular references are indirectly circular and not a simple one like this they are extremely hard to track down), then I suggest you use a 3rd cell for your answer.
i.e. say in A3
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks