As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Options

SkyCaptain
IndianaRegistered User regular

Is there a simple way to average the sum of a set range, but only if a cell has a greater than zero value? For example, I have 10 cells with 4 values not equal to zero. I want a simple formula that will average based on the 4 non-zero values instead of all 10 cells.

The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition

0

## Posts

vch457onwhat you are looking for is an "if then statement"

a dirty way around it would be to add a new column (assuming your data is vertical) in that new column do a Countif cell A> 0 hhmmm damn no excel on this computer

basically add a new column next to your original data, only populate that new column with values >0 (sorry off the top of my head can't remember) then with your new column with only >0 data you can then easily average.

hope this points you in the right direction. That or someone more savvy than me shows up.

Natas_XnoybisonGIS is evil

scrivenerjonesonMinor change, but since he said "greater than 0" it should be this, I think:

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

This way if you have negative numbers you can safely ignore them. If 0 is your floor, though, either formula will work.

jclastonNatas_XnoybisonGIS is evil

finnithonSteam: CavilatRest

focused7onSkyCaptainonim just smart as hell. also good-looking

scrivenerjonesonThe excel help files almost always help me out a lot and answer my questions. Sometimes it takes a while to figure out the right keywords though.

CauldonBlackjackon3DS: 1607-3034-6970

ueanonPSN - sumowot

Inquisitor77onDiannaoChongonSkyCaptainonIs there a way to create a formula that will find the average of expressions like these: 1d6+1, 2d6+2, 3d6+10, etc. Basically, I want to be able to enter in a die roll and see what the average is automatically.

SkyCaptainon1d6+1

turns into

=Randbetween(1,6)+1

You can use this to brute force the answer and repeat this formula across 10,000 columns, then average out those values. But that would be cheating and also very inelegant. God kills a kitten every time you use inelegant Excel solutions.

Inquisitor77onCell A1: 10d60+15 (or whatever)

Cell B1: =FIND("d",A1)

Cell C1: =LEFT(A1, B1-1)

Cell D1: =FIND("+",A1)

Cell E1: =IF(ISERROR(FIND("+",A1)), RIGHT(A1, LEN(A1)-B1), MID(A1, B1+1, D1-B1-1))

Cell F1: =IF(ISERROR(FIND("+",A1)), 0, RIGHT(A1,LEN(A1)-D1))

Cell G1: =(C1*(1+E1)/2)+F1

I don't really want to concatenate this mess into a single formula, although it would be somewhat less ugly if 1d6 was entered as 1d6 + 0, or if separate cells were used for the number of dice, the number of sides and the bonus.

But again, I'm not sure if I'm missing a key piece of math here. I feel that the average should be straightforward -- 3.5 per 6-sided die, 10.5 per 20 sided die, etc. -- although the probabilities of any individual value become more and more convoluted as more dice are involved.

OrogogusonYep, this should be true.

Daenrison(# of Rolls)(Die) + constant

Then you could probably just do a simple formula like this:

A1 = # of Rolls

B1 = Maximum Die Value

C1 = constant

D1 = A1((1+B1)/2)+C1

Unless I'm just reading the die expressions incorrectly? It's like 4am here, why am I even thinking about this...

Inquisitor77onSkyCaptainonSkyCaptainonInquisitor77on