working backwords to restore a formula

tigger2

New Member
Joined
Apr 22, 2002
Messages
33
I have a spreadsheet which I did not create, in which several formulas are returning @VALUE errors. The formulas are supposed to calculate warranty periods. I can't figure out why the formulas are not working. Any ideas?

Q4=IF(CELL("datatype",I4:I4)="l",I4,EDATE(I4,L4)) where I4 is a date and L4 is a number

R4=IF(CELL("datatype",J4:J4)="b","Not Known",EDATE(J4,M4)) where J4 is a date and M4 is a number

S4=IF(R4="Not Known",Q4,MIN(Q4:R4)) depends on R4 & Q4 being correct

T4=IF(CELL("datatype",S4:S4)="l","Undetermined",IF(S4>=TODAY(),"YES","NO")) depends on S4 being correct
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
742
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Are you sure the I4 & J4 are valid dates?

Gnaga
 

tigger2

New Member
Joined
Apr 22, 2002
Messages
33
They are dates in the past, but they are valid dates, and in date format.
 

tigger2

New Member
Joined
Apr 22, 2002
Messages
33
Well, it did something. it now returns @NAME except for R4 - now returns "unknown"

Can someone explain to me what this formula is trying to do? I'm not familiar with the CELL, EDATE and DATATYPE terms here. That might help me figure this out.

Thanks.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
EDATE is part of the Analysis ToolPak, a statistical function Add-In. You are getting #NAME because you haven't loaded the Add-In. To do so choose Tools, Add-Ins, check the box next to Analysis ToolPak and click OK.

To get help on a function select the cell containing it and click the f* button on the Toolbar. Then click the ? button in the Paste Function dialog box.
 

Forum statistics

Threads
1,181,690
Messages
5,931,467
Members
436,790
Latest member
aelbaitam

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
Top