A complicated IF

[sgtbrowne]

I have three columns

• Confidence levels (High, Medium, Low)
• Training Date
• Reassessment Date
  • I would like this column to automatically show the date for the following
    • High: add 90 days to training date
    • Medium: add 60 days to training date
    • Low: add 30 days to the training date

[#2 · Accepted solution]

@sgtbrowne 

As variant

=B1+IFNA(INDEX({30,60,90},MATCH(A1,{"Low","Medium","High"},0)),0)

[#3]

@Sergei Baklan Can I include this in a data validation? If so how?

[#4]

Or is there a way to make it so every time I make a new row, the formula is there automatically?

[#5]

@sgtbrowne 

I didn't catch about data validation, what is the purpose. Is that for Reassessment Date which entered manually?

[#6]

I am using some data validation that allows certain dropdowns to already be present in each row of certain columns. I would like to be able to do the same thing with the formula. Right now, I would have to copy and paste the entry in each time and I would like to make this excel sheet as automated as possible.

[#7]

@sgtbrowne 

If use Excel Tables formulas will be repeated automatically for every new row of the table.

[#8]

Is there a way for me to adjust the if so when the confidence and training date hasn't been inputted, the cell stays blank? Right now, it's putting in 1/0/1900.

[#9]

Actually, can i adjust the if so when the training date column is blank, the reassessment date column is also blank?

[#10]

@sgtbrowne 

Please check in attached file

I'd make helper table (in grey) with levels and days to add. It could be in any place of the workbook, preferably in another sheet. With tah we may add named range

and use it for data validation

formula for the Reassignment date could be

=IF([@[Training Date]]="","",[@[Training Date]]+IFNA(INDEX(Confidence[Days],MATCH([@Confidence],Confidence[Confidence Level],0)),0))

It returns empty string if Training date is empty, otherwise returns new date. Expanding the table formula will be added automatically.

[#11]

@Sergei Baklan When i try to define the reference for the levels as =Confidence[Confidence Level] I get an error message 

[#12]

@Sergei Baklan I have attached my attempt at this thing along with the error message I am getting

[#13]

@sgtbrowne 

Please rename the table as Confidence

and since first column also named as Confidence, use Confidence[Confidence]