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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

My suggestion should do the trick.
 

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,143,617
Messages
5,719,736
Members
422,242
Latest member
hishamkhatri

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