Forum Discussion
If Function Help
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 thanks for your help! I added some additional context to @PReagan reply below.
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.
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
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")))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