IF Formula

J

joeschmoeexceljunkie

New Member
Joined
Dec 6, 2018
Messages
3
Is there a way to create an IF formula holding the false value constant until the IF formula is false? Then after the IF formula is false, remove the constant cell reference?

This may sound confusing so I will explain further.

I am currently showing a retirement plan graph showing contributions to a certain age, then withdraws once retired. I have been able to show this but must manipulate the data if I want to change inputs (i.e. starting age or retirement age)

MY currently formula is this =IF(AR3<$AP$3, I3,$W$3)
AR3= How long they have worked
AP3= # of years worked
I3= The amount of money they have at the end of the year worked (i.e. I3= year 1 worked, I4=year 2 worked, etc.)

I want to drag this formula down but am having issues...because W3 is referencing the amount of money at the end of retirement and is being held constant. Once my IF formula is false, I would like the constant to be removed from W3 to allow the total retirement to be decreased as you draw once you are retired.

Does this make sense? Is this possible?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Might be best if you could provide an example, showing the data that is there, and what your expected result is.
 
Upvote 0
mrshl9898 said:
Might be best if you could provide an example, showing the data that is there, and what your expected result is.
Click to expand...

Goal: I am ultimately trying to figure out how long until my retirement money runs out and at what age. I want this to be universal so that I can easily input my starting age and ending age without having to manipulate my data to show this graphically.

So Below are my numbers (I know they're a little messed up). Through several formulas, I have calculated my baseline which is the total amount in my retirement account. My baseline formula is =IF(AR3<$AP$3, I3,$W$3). W3 is referencing the total amount in my retirement account at the end of retirement. You contribute until you retire. So the formula needs to change but I can't simply change the formula every time because the number of years worked is different case by case. Therefore, W3 is held constant so it does not reference the total remaining balance until the IF function is false. However, I need it to reference W3, W4, W5, and so on as the equations move down the rows when the IF function is false. Is there a way to have the formula remove the constant without having to manually remove it in the formula? That is really the crux of my question.
[TABLE="width: 838"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD] Row Age [/TD]
[TD] Total Remaining Balance [/TD]
[TD]# of Working Years[/TD]
[TD]Age[/TD]
[TD]Years worked[/TD]
[TD]Baseline[/TD]
[/TR]
[TR]
[TD="align: right"]3 62[/TD]
[TD] $ 686,606[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD] $ 4,220[/TD]
[/TR]
[TR]
[TD="align: right"] 4 63[/TD]
[TD] $ 651,977[/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2[/TD]
[TD] $ 8,799[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD] $ 615,963[/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]3[/TD]
[TD] $ 13,760[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD] $ 578,509[/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4[/TD]
[TD] $ 19,128[/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD] $ 539,557[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]5[/TD]
[TD] $ 24,929[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD] $ 499,046[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD] $ 31,193[/TD]
[/TR]
[TR]
[TD="align: right"]68[/TD]
[TD] $ 456,916[/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]7[/TD]
[TD] $ 37,947[/TD]
[/TR]
[TR]
[TD="align: right"]69[/TD]
[TD] $ 413,100[/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD] $ 45,224[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD] $ 367,531[/TD]
[TD][/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]9[/TD]
[TD] $ 53,057[/TD]
[/TR]
[TR]
[TD="align: right"]71[/TD]
[TD] $ 320,139[/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]10[/TD]
[TD] $ 61,482[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD] $ 270,852[/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]11[/TD]
[TD] $ 70,535[/TD]
[/TR]
[TR]
[TD="align: right"]73[/TD]
[TD] $ 219,594[/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]12[/TD]
[TD] $ 80,255[/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD] $ 166,285[/TD]
[TD][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]13[/TD]
[TD] $ 90,686[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD] $ 110,843[/TD]
[TD][/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]14[/TD]
[TD] $ 101,871[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD] $ 53,184[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15[/TD]
[TD] $ 113,857[/TD]
[/TR]
[TR]
[TD="align: right"]77[/TD]
[TD] $ (6,781)[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]16[/TD]
[TD] $ 126,694[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD] $ (69,145)[/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]17[/TD]
[TD] $ 140,434[/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD] $ (134,004)[/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]18[/TD]
[TD] $ 155,133[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD] $ (201,456)[/TD]
[TD][/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]19[/TD]
[TD] $ 170,849[/TD]
[/TR]
[TR]
[TD="align: right"]81[/TD]
[TD] $ (271,607)[/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]20[/TD]
[TD] $ 187,646[/TD]
[/TR]
[TR]
[TD="align: right"]82[/TD]
[TD] $ (344,564)[/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]21[/TD]
[TD] $ 205,588[/TD]
[/TR]
[TR]
[TD="align: right"]83[/TD]
[TD] $ (420,440)[/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]22[/TD]
[TD] $ 224,746[/TD]
[/TR]
[TR]
[TD="align: right"]84[/TD]
[TD] $ (499,350)[/TD]
[TD][/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]23[/TD]
[TD] $ 245,193[/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD] $ (581,417)[/TD]
[TD][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]24[/TD]
[TD] $ 267,007[/TD]
[/TR]
[TR]
[TD="align: right"]86[/TD]
[TD] $ (666,766)[/TD]
[TD][/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]25[/TD]
[TD] $ 290,271[/TD]
[/TR]
[TR]
[TD="align: right"]87[/TD]
[TD] $ (755,529)[/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]26[/TD]
[TD] $ 315,072[/TD]
[/TR]
[TR]
[TD="align: right"]88[/TD]
[TD] $ (847,843)[/TD]
[TD][/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]27[/TD]
[TD] $ 341,501[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD] $ (943,850)[/TD]
[TD][/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]28[/TD]
[TD] $ 369,658[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD] $ (1,043,697)[/TD]
[TD][/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]29[/TD]
[TD] $ 399,644[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[TD] $ (1,147,537)[/TD]
[TD][/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]30[/TD]
[TD] $ 431,569[/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[TD] $ (1,255,531)[/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]31[/TD]
[TD] $ 465,548[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD] $ (1,367,845)[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]32[/TD]
[TD] $ 501,704[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD] $ (1,484,652)[/TD]
[TD][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]33[/TD]
[TD] $ 540,164[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD] $ (1,606,131)[/TD]
[TD][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]34[/TD]
[TD] $ 581,066[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD] $ (1,732,468)[/TD]
[TD][/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]35[/TD]
[TD] $ 624,554[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD] $ (1,863,860)[/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]36[/TD]
[TD] $ 670,779[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD] $ (2,000,507)[/TD]
[TD][/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]37[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD] $ (2,142,620)[/TD]
[TD][/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]38[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD] $ (2,290,418)[/TD]
[TD][/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]39[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD] $ (2,444,127)[/TD]
[TD][/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]40[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]41[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]42[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]43[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]44[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]45[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]46[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]47[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]48[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]49[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]50[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]51[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]52[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]53[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]54[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]55[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]56[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]57[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]58[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]59[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]60[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]61[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]62[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]63[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]64[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]65[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]66[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]67[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]68[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]69[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]70[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]71[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]72[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]73[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]74[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]75[/TD]
[TD] $ 686,606[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]76[/TD]
[TD] $ 686,606[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
So W is "Total Remaining Balance?, what are the headers in AR and AP?

Are your expected results in here? Or just the values you are currently getting?
 
Last edited:
Upvote 0
mrshl9898 said:
So W is "Total Remaining Balance?, what are the headers in AR and AP?

Are your expected results in here? Or just the values you are currently getting?
Click to expand...

correct, W is total remaining balance.
AP= $ of working years
AQ = Age
AR = Years works
AS = Baseline

The numbers my graph is pulling from is column AS (Baseline)
So total remaining balance is what I am pulling from
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
301
zero269
Z
E
  • Question Question
Reference VBA action vs. a cell in a formula?
Replies
2
Views
168
EssKayKay
E
E
  • Question Question
Vesting Schedule - Formula/Formatting
Replies
1
Views
102
StephenCrump
S
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
J
  • Question Question
Formula to change values in different cells - HELP!!
Replies
1
Views
459
awoohaw
awoohaw

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top