working backwords to restore a formula

[tigger2]

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

 

[gnaga]

Are you sure the I4 & J4 are valid dates?

Gnaga

 

[Andrew Poulsom]

Try replacing "datatype" with "type".

 

[tigger2]

They are dates in the past, but they are valid dates, and in date format.

 

[Andrew Poulsom]

My suggestion should do the trick.

 

[tigger2]

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]

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.