Share via

Excel Spred sheet help

Anonymous
2018-06-22T08:04:26+00:00

i am trying to create spreadsheet for my cricket club and i want to create formula for Batsman average ,

But batsman average is calculated as total number of run divided by( number innings - number of not out innings)  

So i am unable to create formula for that,

Also i am looking to enter the not out Batsman run as differently but if i enter it with * at the end it's not totalling that number in total

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2018-06-22T08:51:18+00:00

Hi,

This will sum column B for a batsman in column A even if there is a * at the end of the number. The batsman to sum is in D1 and note it will not handle blank cells in column B

=SUMPRODUCT((A2:A10=D1)*(--SUBSTITUTE(B2:B10,"*","")))

IMHO it would be much simpler to put the * in another column then you can count a batsman runs with:-

=SUMIFS(B2:B20,A2:A20,D1)

and the total innings with

=COUNTIF(A2:A20,D1)

and the not-out innings with

=COUNTIFS(A2:A20,D1,C2:C20,"*")

Was this answer helpful?

0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest