If Function Help

[Nick_Anderson1100]

I am trying to set a value ranking based on sum values for a number of attributes.  Can anyone see something wrong with my formula?

=IFS(D3>=D10,"3",if(D3<D10>D11,"2"),D3<D12,"1"))

its the "2" function that is not working no matter what I try to do.  Basically I want it to display 2 if D3 is less than D10 but greater than D11.

[#2]

@Nick_Anderson1100 

Perhaps

=IF(D3>=D10,3,IF(D3>D11,2,IF(D3<D12,1,"something is wrong")))

For me logic is not clear - if D3 is less than D10 other two conditions (more than D11, less than D12) could happen simultaneously. Shall we handle them in such order?

[#3]

Hello @Nick_Anderson1100,

Alternatively, that could be:

=IFS(D3>=D10,3,AND(D3<D10,D3>D11),2,D3<D12,1)

[#4]

@PReagan  thanks for your help!

The formula worked for 4 of 5 value sets!  the 5th one I get the #N/A error.  Not seeing how that is possible...

Some additional content:

Agent Values:

Agent 1 (D3) = 509

Agent 2 (D4) = 395

Agent 3 (D5) = 273

Agent 4 (D6) = 501

Agent 5 (D7) = 247

Threshold Values:

D10 = 385

D11 = 257

D12 = 128

If Agent Values is greater than D10 = 3

If Agent Values is less than D10 but greater than D11 = 2

If Agent Values is less than D12 = 1

[#5]

@Sergei Baklan thanks for your help!  I added some additional context to @PReagan reply below.

[#6]

@Nick_Anderson1100 

The formula that I supplied only accounts for the criteria:

D3>=D10

D3<D10

D3>D11

D3<D12

Any value that doesn't meet these criteria will return an error (#N/A).

To accommodate for this, I suggest using the formula that @Sergei Baklan provided.

[#7]

Hello @PReagan and @Sergei Baklan 

I've tried both your recommendations and both return an error on the 5th Agent.  understanding that the formula provided was for D3, I am trying to copy the logic across D4,5,6,and 7

[#8]

@Nick_Anderson1100 

Without seeing your data, it is difficult to propose a proper solution. Nonetheless, maybe this is what you're looking for?

=IF(D3>=$D$10,3,IF(D3>$D$11,2,IF(D3<$D$12,1,"something is wrong")))

[#9]

@PReagan Agent 5 still receives the error "something is wrong"

Would the fact that D10,11,and 12 have their own SUM formulas impact this ?

Agent Values:

Agent 1 (D3) = 509

Agent 2 (D4) = 395

Agent 3 (D5) = 273

Agent 4 (D6) = 501

Agent 5 (D7) = 247

Threshold Values:

D10 = 385

D11 = 257

D12 = 128

If Agent Values is greater than D10 = 3 points

If Agent Values is less than D10 but greater than D11 = 2 points

If Agent Values is less than D12 = 1 points

I am attempting to populate the Points into a range of cells (say D14, 15, 16, and 17) for each Agent

[#10]

@Nick_Anderson1100 

Agent 5 (D7) = 247

247 is greater than 385; FALSE

247 is less than 385 but greater than 257; FALSE

247 is less than 128; FALSE

Agent 5 fails all criteria, hence returning false or "something is wrong". Perhaps you should revisit your criteria to account for this.

[#11 · Accepted solution]

@PReagan Success!!  after staring at these numbers I must have missed that.  (good catch)

small tweak and it now works!  =IFS(D3>=D10,3,AND(D3<D10,D3>D12),2,D3<D12,1)

Thank you very much for your help here!

[#12]

@Nick_Anderson1100 

Wonderful!! Glad to be of help!