Need a formula for a sliding scale

[kafarrell]

On Sheet "Current" in Column K I need it to say (K5)
if J5<>"DOM",J5
if it does equal "DOM" then if the total volume (Column G) of Port 1 (Column J) for DOM is < 100 then DOM1
if the total volume (Column G) of Port 1 (Column J) for DOM is 100 to <250 then DOM2
if the total volume (Column G) of Port 1 (Column J) for DOM is 250 to <500 then DOM3
if the total volume (Column G) of Port 1 (Column J) for DOM is 500 or > then DOM4

I know this should be fairly easy, but I can't figure it out.

Thank you

[Speshul]

If I follow the question, try using a LOOKUP function instead of IF's

=IF(J5<>"DOM",J5,LOOKUP(G5,{0,100,250,500},{"DOM1","DOM2","DOM3","DOM4"}))

Lookup will match G5 to the largest value in Array 1 that is not larger than G5, and return the corresponding index in Array 2


If G5 is 174
Where array 1 is
{0,100,250,500}
And array 2 is
{"DOM1","DOM2","DOM3","DOM4"}

174 will match with 100, the second index in array1 (because it is the largest number not greater than 174), and return DOM2, which is the second index in array 2

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

[kafarrell]

Both of those would work EXCEPT that it needs to be the Total Vol of DOM, not just 1 line, so if there are 2 lines for DOM it needs to add those to see what the total is.

[kafarrell]

This would work EXCEPT that it needs to be the Total Vol of DOM, not just 1 line, so if there are 2 lines for DOM it needs to add those to see what the total is.

Hi,

Try

Please Login or Register  to view this content.

[Speshul]

So something like this?


=IF(J5<>"DOM",J5,LOOKUP(SUMIF(Table2[Port 1],"DOM",Table2[Total Vol]),{0,100,250,500},{"DOM1","DOM2","DOM3","DOM4"}))

[kafarrell]

PERFECT! Thank you so much!

So something like this?


=IF(J5<>"DOM",J5,LOOKUP(SUMIF(Table2[Port 1],"DOM",Table2[Total Vol]),{0,100,250,500},{"DOM1","DOM2","DOM3","DOM4"}))