How To Edit Small Array To Show Values Only Under 50

aedctalk

Board Regular
Joined
Oct 9, 2010
Messages
156
Hello!

I am using this small array.. but i just want to show numbers that are

not over 50

AND

not UNDER negative 50

.. how could i modify it to work in that way? I'd still like to keep the F114:AS114=0 part of it as well.. :/


={SMALL(IF(F114:AS114=0,AX145,F114:AS114),1)}


Thank you so so much for insight or help on this! Really really appreciate it!!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hard to follow...

Are you wanting to calculate the smallest value from F114:AS114 and AX145, disregarding values above 50 and under -50?
 
Upvote 0
Hello!

disregarding values above 50 and under -50? Yes.

however AX145 is the cell value i want to show up if SUM(F114:AS114)=0 or if the number is above 50 or under -50

sorry. thank you for your help :)
 
Upvote 0
ax145 just says =0 i dunno i guess i could just fit that into the array instead of having it point to ax145 :/
 
Upvote 0
Thank you.

How would i fit this


=IF(SUM(F114:AS114),IF(ABS(MIN(F114:AS114))>50,0,MIN(F114:AS114)),0)

within my original small array though? to pull the 2nd highest value within F14:AS114?

technically i guess if the value does not meet the +50 to -50 range set below then i would want the cell to be ignored.

I have a chart of top 2 values

={SMALL(IF(F114:AS114=0,AX145,F114:AS114),1)}
={SMALL(IF(F114:AS114=0,AX145,F114:AS114),2)}

and wouldnt want these chart values to be turned into 0 if they didnt meet the +50 and - 50 range.. i would want it to not consider it and move on to the next smallest value

Is that possible?


={SMALL(IF(F114:AS114=0,AX145,F114:AS114),1)}
 
Upvote 0
Thank you.

How would i fit this


=IF(SUM(F114:AS114),IF(ABS(MIN(F114:AS114))>50,0,MIN(F114:AS114)),0)

within my original small array though? to pull the 2nd highest value within F14:AS114?

technically i guess if the value does not meet the +50 to -50 range set below then i would want the cell to be ignored.

I have a chart of top 2 values

={SMALL(IF(F114:AS114=0,AX145,F114:AS114),1)}
={SMALL(IF(F114:AS114=0,AX145,F114:AS114),2)}

and wouldnt want these chart values to be turned into 0 if they didnt meet the +50 and - 50 range.. i would want it to not consider it and move on to the next smallest value

Is that possible?


={SMALL(IF(F114:AS114=0,AX145,F114:AS114),1)}

If you substitute SMALL for MIN...

=IF(SUM(F114:AS114),IF(ABS(SMALL(F114:AS114,1))>50,0,SMALL(F114:AS114,1)),0)

do you achieve what you want?
 
Upvote 0
Thanks for your continued help.

It works fine for finding the smallest values.

And yes if the value is over 50 or below -50 it does turn it to 0

However

Now on my "Top shortages chart"

Those two that were over 50.. show 0

However i have many more values within that same range that are -5 or +10 ... I would like those to show up as the SMALL value instead of the large values that i turned to 0 .. showing up in my chart. basically i guess i need another stipulation that the SMALL is NOT equal to 0?

Maybe that would do the trick?
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top