Showing a dash to represent a zero

[astole]

=IF($C$2="NO STACK/SNOWBALL","-",
IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R95=SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1)),$BA101,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",R96=MAX($R96:$AC96)),$C75,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",R96=MAX($R96:$AC96)),$C75+$AO101,0))))

How do I get the second formula to show a dash if R95-AC95 all show dashes representing a zero?

[martindwilson]

=SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1) will return an error if all 0 or all -

[astole]

Didn't work

[martindwilson]

what didn't work? im just saying that part of the formula will always be an error if $R95:$AC95 are all 0 or -
so
,R95=SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1)
would evaluate as

R95=#NUM! so if you leave c2 blank or try anything else in there EXCEPT NO STACK/SNOWBALL the formula will give #NUM!
so you need to decide what to do if its an error
probably easiest is to wrap it in iferror and give a value r95 cannot = say "x"
then change the last bit from 0 to -
=IF($C$2="NO STACK/SNOWBALL","-",
IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R95=IFERROR(SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1),"x")),$BA101,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",R96=MAX($R96:$AC96)),$C75,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",R96=MAX($R96:$AC96)),$C75+$AO101,"-"))))

[astole]

I had IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R95=SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1)),$BA101

This is almost the same except the beginning and ending
=SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1)

I need the same formula except to produce a dash if R to AC are all dashes or zeros

[astole]

That worked, the x did it, thank you for clarifying

[martindwilson]

read my last post

[astole]

If you say that it will only for work for the 2nd, to show dash if they are blank or have a dash, then how would you do it for the thrid and fourth to show a dash if R to AC are dashes?

=IF($C$2="NO STACK/SNOWBALL","-",
IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R95=IFERROR(SMALL($R95:$AC95,COUNTIF($R95:$AC95,0)+1),"x")),$BA101,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",R96=MAX($R96:$AC96)),$C75,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",R96=MAX($R96:$AC96)),$C75+$AO101,"-"))))

[astole]

I tried doing what you said with the third and fourth but it didn't work (putting the iferror and the X)

=IF($C$2="NO STACK/SNOWBALL","-",
IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R46=IFERROR(SMALL($R46:$AC46,COUNTIF($R46:$AC46,0)+1),"x")),$BA52,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",R47=IFERROR(MAX($R47:$AC47)),"x",$C26,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",R47=IFERROR(MAX($R47:$AC47)),"x",$C26+$AO52,"-"))))

[astole]

I'm assuming like this??

=IF($C$2="NO STACK/SNOWBALL","-",
IF(AND($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",R46=IFERROR(SMALL($R46:$AC46,COUNTIF($R46:$AC46,0)+1),"x")),$BA52,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",R47=IFERROR(MAX($R47:$AC47),"x")),$C26,
IF(AND($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",R47=IFERROR(MAX($R47:$AC47),"x")),$C26+$AO52,"-"))))

[astole]

That worked, I'm wondering why it will not work for other formulas such as this? Where if the 37 and 44 are blanks or one is blank...showing an error

=MAX(IF($C$2="NO STACK/SNOWBALL", S37,
IF($C$2="STACK SET PLUS (OPT) EXCESS (NO SNOWBALL)",S37-S44,
IF($C$2="SNOWBALL SET PLUS EXCESS (LOWEST BALANCE)",S37-S44,
IF($C$2="STACK SET PLUS (OPT) EXCESS (SNOWBALL)",MAX(IF(S$8=0,0,ROUND(S37-S44,2)),0),0)))),0)

The IFERROR and "X"...

[martindwilson]

i can only suggest at the moment replacing
R96=MAX($R96:$AC96)
with
R96=IF(COUNTIF($R96:$AC96,"-")=12,"x",MAX($R96:$AC96))