IF AND Statement

[dannytt93x]

Hello,

I am trying to create a tool which dependent on year, will tell me whether to upgrade a phone or repair.

I have go to it work with the year so that it either returns "repair and Replace" dependent on year... but i want to incorporate an and statement in this.

the statement below is what i currently have :

=IF(SUM(COUNTIF(D12,{"2017","2018","2019"}))*(C12=A2),D2,E2)

D2 = Repair E2 = Upgrade

D12 is a year in a drop down menu.

I am trying to do it so i can select phone from drop down... : iPhone 8 , iPhone XR, iPhone X, iPhone 11, iPhone 11 pro. with the repair option shown if iPhone 8 year is less than 2020 but replace if the year is more than that.

iPhone 8 - Repair if year is 2019 or less - upgrade if 2020 or above
iPhone XR - Repair if year is 2020 or less - upgrade if 2021 or above
iPhone X - Repair if 2019 or less - upgrade if 2020 or above
iPhone 11 - Repair if 2021 or less - upgrade if 2022 or above
iPhone 11 pro - Repair if 2020 or less - upgrade if 2021 or above

the question is, can this be all out in one statement so that when i select specific options in excel, it tells someone whether to repair or upgrade.

Both formula and VBA or options.

Any help would be greatly appreciated.

Kind Regards

 

[Toadstool]

You don't say what C12 and A2 are so I'll ignore them.

I would use a table with the phone model and the year after which you Upgrade. That table could be on another worksheet and if you used an Excel Table it would look prettier, but I'll stick with cell references.

	D	E	F	G	H	I	J	K	L	M
Action 1	Action 2	Phone
Repair	Upgrade	iPhone 8
iPhone XR
iPhone X
iPhone 11
iPhone 11 pro
Phone	Action
iPhone 8	Repair

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Trigger Year[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2020[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2021[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2020[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Year[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

dannytt93x​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=IF($D$12<=INDEX($M$2:$M$10,MATCH($E$12,$L$2:$L$10,0)),$D$2,$E$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dannytt93x]

Sorry, C12 is the cell where to drop down list is using data validation A2 is referencing the list where the device.

The formula works great... but is there any way to have it so they can pick from consecutive years rather than trigger years? so they would pick the year they are currently in, pick the device, and then it would give them an action of either repair or upgrade?

Kind Regards

 

[Toadstool]

I'm sorry but I don't understand "C12 is the cell where to drop down list is using data validation A2 is referencing the list where the device".

In my sheet cell D12 is a Data Validation list of consecutive years, 2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024

Cell E12 is a Data Validation list with source of L2:L10.

...so it currently lets them pick a year, pick the device and gives them the action.

 

[dannytt93x]

apologies, i had copied the wrong cells wrong on my sheet and had referenced the trigger year rather than a list of years.

Thank you so much for this, absolute legend!