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

Excel Formula Help - Not homework...

SkyCaptainSkyCaptain IndianaRegistered User regular
edited August 2010 in Help / Advice Forum
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
    vch457vch457 Registered User regular
    edited May 2010
    you can try this: =SUM([range])/COUNTIF([range],">0")

    vch457 on
  • Options
    Natas_XnoybisNatas_Xnoybis 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
    scrivenerjonesscrivenerjones Registered User regular
    edited May 2010
    =SUM(A1:A10)/COUNTIF(A1:A10,"<>0") or whatever the range is. booya.

    scrivenerjones on
  • Options
    jclastjclast 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
    camo_sig2.png
  • Options
    Natas_XnoybisNatas_Xnoybis 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
    finnithfinnith ... 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
    focused7focused7 Registered User regular
    edited May 2010
    =AVERAGEIF(A1:A10,">0",A1:A10)

    focused7 on
  • Options
    SkyCaptainSkyCaptain 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
    scrivenerjonesscrivenerjones 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
    CauldCauld 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
    BlackjackBlackjack 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
    camo_sig2.png

    3DS: 1607-3034-6970
  • Options
    ueanuean 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
    Inquisitor77Inquisitor77 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
    DiannaoChongDiannaoChong 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
    steam_sig.png
  • Options
    SkyCaptainSkyCaptain 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
    SkyCaptainSkyCaptain 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
    Inquisitor77Inquisitor77 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
    OrogogusOrogogus 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
    DaenrisDaenris 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
    Inquisitor77Inquisitor77 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
    SkyCaptainSkyCaptain 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
    SkyCaptainSkyCaptain 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
    Inquisitor77Inquisitor77 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? D:

    Inquisitor77 on
Sign In or Register to comment.