IF Statement with multiple line item

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
Hi,

Can you please assist with an IF statement that would have multiple conditions for analysis.

I currently have 3 customers with an associated Part Number. I am trying to find a IF statement that would say if customer ex.A,B,C price is within $10 of each other then "Yes" if not "No". I have listed what I wanted below.



Customer NamePart NumberPrice Me UnitIf Statement what I WANT
AES Electrical, Inc. (Platinum Pkg)5C90813Yes
FRESNO, City of5C90820Yes
M&M Management5C9086.99No

<colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Isn't M&M price within $10 of AES ($6.99 vs $13)?
 

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91

ADVERTISEMENT

I apologize I meant $20 so all with "No". That was a TYPO
 

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
Main thing for the formula is to analyze customer ex (A, B, C) together in 1 IF statement and have the threshold of $20 for each of them. And if they meet within the threshold of $20 then YES and if not then NO
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

if 20 then they will all be Yes!
 

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
So basically if Customer A is within $20 of Customer B&C and Customer B is with $20 of Customer A&C and lastly Customer C is with $20 of Customer A&B
 

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
So in this case AES unit price is $13 which is not with customer Fresno & M&M if we have threshold of $20 applied
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Maybe

=IF(A2="","",IF(ABS(E2-MIN(IF($E$2:$E$40<>"",IF($A$2:$A$40<>A2,$E$2:$E$40))))<20,"Yes","No"))

Enter as an array, Ctrl, Shift & Enter
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,587
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top