SOLVED! Mimic VBA IF using =IF

I know VBA but can't use it in my current situation.

I want to check a total versus a record total and store the new record if the current total exceeds the record total. In VBA the test would look like this:

If Range("C1")>Range("D1") then Range("D1")=Range("C1")

I tried =IF(C1>D1,D1 = C1) but it appears to be treating the equation in the TRUE area as a second logical test. Since C1 can't be > and = D1 at the same time the cell shows the logical result of FALSE no matter what values are in C1 and D1.

In an attempt to see if this is possible I tried putting the following in Cell E1: =If(C1 = D1, F1 = "Cat", G1 = "Dog"). Then I changed the values of C1 and D1 but E1 constantly displayed FALSE. If I removed the Cell references in the =IF it puts "Cat" or "Dog" in E1. It appear to me that Excel does not accept cell references in the TRUE or FALSE areas of the =IF statement.

As far as I can tell what I'm asking is impossible but I thought I'd ask in case there is a syntax trick I can use or some other function besides =IF i can use? Swapping out record highs and lows is important in statistics so it seems to me there should be a way to do it so I'm hoping one of you has some insight.

Thanks.

Hi,

You have to use a circular reference to achieve what you are trying to achieve. A circular reference means that a formula in a cell, refers either to that cell itself, or to a cell that refers to that cell. The formula you will need in cell D1 is:

Formula:

Please Login or Register  to view this content.

You will receive a warning about circular references, and the value will return as 0 initially. For this formula to work, you must enable iterative calculation. In Excel 2010: File --> Options --> Formulas --> Enable iterative calculation (tick).

I hope this helps

Hi and welcome to the forum

Formulas cannot "push" answers to another cell, they can only pull data. So if you want F1 = "Cat", then you need to put a formula IN F1 to return that answer, something like =IF(C1=D1,"Cat","")
Likewise in G1 you would have just the opposite =IF(C1<>D1,"Dog","")

I know VBA but can't use it in my current situation.

Not sure your reasons for that statement, but Im sure amnty members here could put some VBA together to do what you want

Perhaps if you provided a more realistic sample, we couild come up with something better?

FD: Essentially I have a range that calculates accrued time off. Sick Time, Comp Time, Vacation Time, etc. and adds these totals together. One of the ranges goes into the future until 2025. Here's a simplified version of the data:

[The spaces get condensed so the chart doesn't display correctly. A is Date, B is the Vacation Total on that date, C is Title of the Time, and D is the hours accrued]

A B C D
01 01/01/14 5 Current Total = 55 [Calculated from Data Below]
02 01/15/14 10 Current Record = 65 [Since Com and Sick are constant for purposes of the example, the high Vac is 25 on 02/25/14]
03 01/29/14 15 Current Vac = 15 [Search on the Range using Today()]
04 02/12/14 20 Current Com = 20 [Manually entered]
05 02/26/14 25 Current Sick = 20 [Manually entered]
06 03/12/14 15
07 03/26/14 20
08 04/09/14 25
09 04/23/14 30

I was going to use the MAX function on the Vacation Data Range but that would return 30 instead of 25 (I won't reach 30 until 04/23 and it is only 03/06). I was going to mess around with using MAX and Today() in concert if I couldn't find a solution using =IF.

In the example above using AJ's solution I entered =If(D1>D2,D1,D2) in cell D2 so D2 is static as D1 Changes up and down as I use the different times at different rates. If I used =if(D1>D2,D1,D2) without turning on the Interactive Calc, D2 is always 0. If I used something like =If(D1>D2,D1,D2) in cell D3 then D3 defaults to D1 if it is greater than D2, which is correct, but since D2 doesn't change once D1 exceeds D2 all the changes above the value in D2 are lost. Let's say I set D2 to 15 and D1 goes from 20 to 25 then to 30. D3 would show 20, 25, and 30, which is correct, however, if D1 then dropped to 20, then D3 would indicate the record to date is 20 when the correct record up to that point would be 30 so D2 needs to store the largest value of D1.

I hope this makes sense and I appologize for the crudity of the example. It is very simple relative to the actual table. I'm sorry if all I did is confuse the situatoin further.