Forum Discussion

Copper Contributor

Mar 05, 2020

Solved

If Function Help

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...

Formulas and Functions

Nick_Anderson1100
Mar 06, 2020
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!

SergeiBaklan

Diamond Contributor

Mar 05, 2020

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?

Nick_Anderson1100

Copper Contributor

Mar 06, 2020

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

PReagan
Bronze Contributor
Mar 06, 2020
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 SergeiBaklan provided.
- Nick_Anderson1100
  Copper Contributor
  Mar 06, 2020
  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
  - PReagan
    Bronze Contributor
    Mar 06, 2020
    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")))