• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Avoiding circular reference

H

Hooroy

New Member
Col. B in the sample sheet below has a user-defined formula that queries the web to update stock prices. I need a formula in Col. C that updates the amount in Col. C whenever Col. B becomes greater than Col. C. For example, in the sample sheet, assume Col. B is updated with new prices and the price in cell B3 becomes 87.33, which is higher than the 86.59 in C3. I need a formula in cell C3 that automatically changes the amount in that cell to be 87.33.

I tried this formula:
=if (B3>C3,B3,C3)
but that gives a circular reference error (and displays “0.00”), apparently because C3 is referring to itself.

Can anyone help? I would like to do this with a formula, not using VBA.
Thanks in advance.


....... A.............B............C
1... Stock.....Current...Highest
....................Price......Price
2 Acme Corp. 45.32 46.74
3 Jones Corp. 87.33 86.59
4 Zebra Corp. 62.82 64.09
 
Last edited:
Hi,

Welcome to the forum..
In this specific case, You have to use a helper column, lets say D, in this column use your formula. "=if (B3>C3,B3,C3)", coz, excel need one physical area, to compare Col C, with Col B,
BTW. to get the value you are using a UDF, and You can change your UDF, to get the expected result
 
Thank you Debraj and Narayan. I think the helper column approach will work fine. I'd rather do this than change the UDF, but that's a good idea too.
 
Debraj,

OOPS, I replied too soon. Now that I'm back home and in Excel, I can't get either the helper column or the UDF solution to work.

As to the helper column, such as D, my formula ("=if(B3>C3,B3,C3)") works fine in helper cell D3, but I need to ultimately get the final answer (87.33) into C3, and I can't see how to get it there from D3.

As to revising the UDF, it is my understanding that since this is a function, it can only report back to the cell in which it resides (B3 in the example) and no other cell. I understand that a function procedure (as opposed to a subroutine) cannot change anything else on the sheet. So a function in B3 cannot put the 87.33 into cell C3, which is the desired result.

Am I missing something?

Thanks again for your help.

hooroy
 
You must log in or register to reply here.
Back
Top