Nov 30 2020 08:53 AM
E F G H I J K
Win | 0.5 | -130 | 0.38 | $1.38 | ||
Win | 0.5 | -145 | 0.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.
Nov 30 2020 09:13 AM
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.
Nov 30 2020 09:40 AM
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.
Nov 30 2020 11:33 AM
Nov 30 2020 12:48 PM
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