I have a long IF statement and need to return blank so I can use those cells for another statement

New Contributor

 

E                         F                  G                    H                     I                 J                            K

Win0.5-1300.38$1.38 
Win0.5-1450.34$1.34 
 1 #DIV/0!#N/A#N/A#N/A
   #DIV/0!#N/A#N/A#N/A

 

I am fairly new to this but so far I have most of this down. I am trying to make it do the Div/0  and #NA show blank so I can add on another Equation further down and return something other than N/A as the answer

 

Basically Cell H formula looks as such,=IF(G4>1,'Totals Hidden'!$C$3*'Unit money'!F4*('Unit money'!G4/100), 'Totals Hidden'!$C$3*F4/(-G4/100))

this returns a possible payout based on money in and line odds.

 

Cell I  looks as=IFS(E4="win",H4+(F4*'Totals Hidden'!$C$3), E4="Lose",-F4-1*('Totals Hidden'!$C$3))

This shows total money lost or won based on Payout Plus bet amount 

Cell J is=I4+('Unit money'!F4*'Totals Hidden'!$C$3)

Which is total payout (or money lost based on results)

 

I am trying to get a running total of each column but the Div and N/A results keep it from running a total other than N/A. and I just want to either count only the cells with number value and/or Have those errors return as a 0 value 

 

Best.

5 Replies

@Dmills958 

 

Would you be willing to post the actual spreadsheet? Otherwise you're asking us to recreate our own, which may or may not accurately represent yours. Doing so would make it easier for anybody here to help you.

@Dmills958 

It's better to wrap your initial formulas with IFERROR() to return zero or empty text instead of errors. SUM() ignores all texts.

But if you need to show errors you may use something like =AGGREGATE(9,6,range) to sum ignoring errors.

@mathetes Let me know if that opens for you.  
Thanks

@Dmills958 

In column J it could be

=IF(ISNA(I2),0,I2)+(F2*'Totals Hidden'!$C$3)

@Dmills958 

 

I took the version that @Sergei Baklan  had given you and added another useful feature, one that makes your formulas a bit shorter AND easier to read. That's a feature that's called a "Named Range."

 

Where you were referring to 'Totals Hidden'!$C$3 in your formulas, I named the value in that cell as BetUAmnt, so now a formula using it looks like this:

=IF(G2>1,BetUAmnt*'Unit money'!F2*('Unit money'!G2/100), BetUAmnt*F2/(-G2/100))

 

Similarly, I took your references to 'Totals Hidden'!$C$8 and replaced them with StrtBnkrll so the one formula that refers to cell C8 now reads:

=StrtBnkrll+'Unit money'!J2