Return max value ONLY when adjacent cell is blank

[LowBorn]

I want to find the latest date in a given row (latest date will always be the right-most value in the range).

A) if that date is in a "Received" column (Columns I, K, M, O, or Q), I want to populate the corresponding cell in the S column with a date that is 14 days later.

B) if that date is in a "Sent" column (Column J, L, N, P, or R), I want to populate the corresponding cell in the T column with the number of days since it was sent.

C) There would never be an instance where the cells in Column S and T in the same row would both have a value. When one of them has a value in it, I want the other to be blank (as shown).

I have been searching through videos on MAX, IF, COUNTIF, XLOOKUP...etc. functions and cannot seem to write such a formula that solves this problem. But perhaps I am not framing this problem correctly.

Any help would be greatly appreciated.

 

[DanteAmor]

Hi and welcome to MrExcel and happy new year

Try this array formula:

Dante Amor
	I	J	K	L	M	N	O	P	Q	R	S	T
1	Rec 1	Sent 1	Rec 2	Sent 2	Rec 3	Sent 3	Rec 4	Sent 4	Rec 5	Sent 5	Due	Days
2	20/oct/22	05/ene/23										1
3	08/jun/22	17/jun/22	21/dic/22								04/ene/23
4	11/nov/22	14/dic/22										23
5	19/sep/22	30/sep/22	07/dic/22	09/dic/22								28
6	16/dic/22										30/dic/22
7	n/a	18/dic/22										19
Hoja3

Cell Formulas
Range	Formula
S2:S7	S2	=IF(MAX($I2:$R2)=MAX(IF(MOD(COLUMN($I$1:$R$1),2)=1,$I2:$R2)),MAX($I2:$R2)+14,"")
T2:T7	T2	=IF(MAX($I2:$R2)=MAX(IF(MOD(COLUMN($I$1:$R$1),2)=0,$I2:$R2)),TODAY()-MAX($I2:$R2),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
------------------

 

[LowBorn]

@DanteAmor Wow, this works great! I really appreciate it.

I am still practicing how to read Excel functions. It looks like you defined the max for each row in the beginning of this formula in =IF(MAX($I2:$R2)=... then the rest instructs what to do with that given value. I was not familiar with the MOD and COLUMN functions, but now after researching them, they are pretty neat. COLUMN looks at the column number (A=1, B=2, etc.), and MOD asks if it is divisible by 2. Then it uses the 1 or 0 to determine if that cell will be blank (use '1'), or if it will show the returned value (use '0'). Right after that it (as part of the IF function), it directs what that value will be. Since there are two unique desired outputs, we have two functions.

This answered my query, and has given me a lot to think about.