Options

# Excel Formula Help - Not homework...

IndianaRegistered User regular
edited August 2010
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
SkyCaptain on

## Posts

• Options
Registered User regular
edited May 2010
you can try this: =SUM([range])/COUNTIF([range],">0")

vch457 on
• Options
Registered User regular
edited May 2010
meh not at work where I have a stockpile of saved formulas.. and work blocks PA :P

what 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_Xnoybis on
I hate Computers
GIS is evil
• Options
Registered User regular
edited May 2010
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0") or whatever the range is. booya.

scrivenerjones on
• Options
Registered User regular
edited May 2010
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0") or whatever the range is. booya.

Minor 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.

jclast on
• Options
Registered User regular
edited May 2010
..... That or someone more savvy than me shows up.

Natas_Xnoybis on
I hate Computers
GIS is evil
• Options
... TorontoRegistered User regular
edited May 2010
Is there some resource you guys use to figure out all these formulas?

finnith on
Bnet: CavilatRest#1874
Steam: CavilatRest
• Options
Registered User regular
edited May 2010
=AVERAGEIF(A1:A10,">0",A1:A10)

focused7 on
• Options
IndianaRegistered User regular
edited May 2010
I am teh dumb. I knew about COUNTIF and it just totally escaped me that I could do the math that way instead of using AVERAGE() or something. Thanks! For those interested, this is going into my budget spreadsheet to show what my average paycheck is over the course of a year since I get overtime often enough that my paycheck is rarely the same.

SkyCaptain on
The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition
• Options
Registered User regular
edited May 2010
finnith wrote: »
Is there some resource you guys use to figure out all these formulas?

im just smart as hell. also good-looking

scrivenerjones on
• Options
Registered User regular
edited May 2010
finnith wrote: »
Is there some resource you guys use to figure out all these formulas?

im just smart as hell. also good-looking

The 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.

Cauld on
• Options
Registered User regular
edited May 2010
finnith wrote: »
Is there some resource you guys use to figure out all these formulas?

im just smart as hell. also good-looking
Modest, too!

Blackjack on

3DS: 1607-3034-6970
• Options
Registered User regular
edited May 2010
I usually use google groups for help, in combination with the excel built in help. Those two resources will get you everything you ever need.

uean on
Guys? Hay guys?
PSN - sumowot
• Options
2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
edited May 2010
It's just like programming, really. Break down what you want to do into discrete logical steps, and then figure out the syntax to put it all together. That's what I do, anyway...

Inquisitor77 on
• Options
Registered User regular
edited May 2010
I just use google for the really hard stuff that I can't think of a quick way to do. 99% of the time someone already had the same issue and has figured it out.

DiannaoChong on
• Options
IndianaRegistered User regular
edited May 2010
Normally I can figure these problems out on my own, but I was looking at one tree in the forest instead of all my options.

SkyCaptain on
The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition
• Options
IndianaRegistered User regular
edited August 2010
More excel help required!

Is 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.

SkyCaptain on
The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition
• Options
2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
edited August 2010
If you want the true average, you'll have to re-conceptualize the problem...I'm not sure a step-by-step translation would work. For example, if you juse Randbetween() as your die:

1d6+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.

Inquisitor77 on
• Options
San DiegoRegistered User regular
edited August 2010
Isn't the average of any given die roll just (min + max) / 2? I was going to say that he could just do something like this:

Cell 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.

Orogogus on
• Options
Registered User regular
edited August 2010
Orogogus wrote: »
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.

Yep, this should be true.

Daenris on
• Options
2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
edited August 2010
The solution to this depends on the format of the dice rolls being evaluated. If the format is always:

(# 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...

Inquisitor77 on
• Options
IndianaRegistered User regular
edited August 2010
Awesome, that should work. Thanks!

SkyCaptain on
The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition
• Options
IndianaRegistered User regular
edited August 2010
Yup, that worked perfectly. Now I can experiment easier to find average rolls to make a smooth damage chart for my maptool rpg.

SkyCaptain on
The RPG Bestiary - Dangerous foes and legendary monsters for D&D 4th Edition
• Options
2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
edited August 2010
Congrats! ...how come the Excel formulas I come up with at 4am work better than the ones I come up with at work?

Inquisitor77 on