Compare 2 cells, then update if greater than

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:19 PM GMT

Question

Thank you for your help on what is likely a basic and dumb question. I've researched and can't get the apparent solutions to work. I would like a formula to compare 2 cells in my spreadsheet and, if A5 is greater than C5 then C5 is changed to now show the data from A5. Hope this makes sense. I appreciate your assistance to a newbie. Gary

Answer

Gary,

this is actually much more complex than perhaps you realize. A cell can hold a value or it can hold a formula. A formula can only return a value to the cell it is in.

so C5 would need to hold a formula. Let's say that you want C5 to have the value 10 and that value will only change if the value in A5 becomes greater than 10. Then you could do

=if(A5>10,A5,10)

But there is a possibility your are trying to build an accumulator cell. Where C5 will always hold the highest value ever held by A5 and if A5 increases, it increases to store that new high value.

If you are trying to do that, then you need to have a formula in C5 that refers to C5.

=if(A5>C5,A5,C5)

If you put that formula in C5, you will get a circular reference error. To avoid that, you have to bring up the Calculation Tab in Tools =>options in the menu (in xl2003 and earlier, in Excel 2007 and later,options are on the bottom right rim of the dialog when you click the office button. Click on that and then select Formulas and in the first section you can find caculation settings.) You want to click on iterations (in Excel 2007, "Enable Iterative Calculations") and set maximum interations to 1. This is called intentional circular references

Now you can put in a formula like the above and not get an error.

You will probably have to reset this everytime you open that workbook.

hope that provides you the information you need.

Advertisement

©2021 eLuminary LLC. All rights reserved.