Announcement

Collapse
No announcement yet.

MS Excel Formula help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    MS Excel Formula help

    I am working on a complex conditional equation where if a cell = no then do this but if the cell is yes, and a second cell is greater then zero do this but if the second cell is less than zero do that.

    The attached file has some play data. I can get all of them except when the Adj Need < 0. Does anyone have any suggestions for a single formula that I can use in all cases to compute the over amount? In the excel file below, the over amount for item 4 is wrong.
    Attached Files

    #2
    I can probably help, but need to understand what it is you are trying to do better. The formula is working as written, but what exactly are you trying to get in the "over" column?

    Comment


      #3
      Tagged

      Comment


        #4
        Originally posted by rladner View Post
        I can probably help, but need to understand what it is you are trying to do better. The formula is working as written, but what exactly are you trying to get in the "over" column?
        I'm trying to get the correct over amount if the adjusted need is negative. For line 4, the current formula is adding to the supplement amount.

        The supplement amount cannot exceed the need. So for example on line 1, initially they only needed $1429 but were given a supplement of $3,428 resulting in an overage of $1,999. (3428-1429=1999). And adjustment was made to their resources and the new need became $3,332. (This is where I realized the "No" in the "Over?" column was hard coded and not formula based. In reality, it should be "Yes" for item 1.) Once I put the formula in place, it changed to Yes and the amount is $96.

        But, if need is negative, the amount over cannot include the negative need otherwise item 4 would have to give up their resource to apply for a supplement. Does that make sense? Item 4's overage can only be the the amount supplemented.

        Maybe I should just ad another column that computes the adjusted amount over using the original formula for "Over" and drop the condition of the yes / no.

        Comment


          #5
          I'm certain I can help but I don't understand the "story". Something like: The cost to take care of the cows and pigs added together is a value. They have some amount of money already (source?) to take care of those animals resulting in a surplus or deficit. Agency or group give them a supplement that is apparently not connected to their actual need. (must be a government program) The resulting amount of total resources they have (adjusted resources) is *no idea as there is no formula* and it breaks down here for me as to what you are trying to do.

          Comment


            #6
            Ok, pretty much what you said:

            Farmer wants to buy cows and pigs that cost the amount in the D, E.
            Farmer has resources of amount in F.
            They have a need of cost of cows and pigs less the resource. If the resource exceeds the cost, they were not eligible for the grant. IE, if the need is negative, they are not eligibile.
            Grant amount = supplement provided.
            The sponsor's agent messed up and provided the over amount in column G.
            Farmer has an opportunity to adjust their resources because they didn't realize some forms of income (income for spouse's job) didn't count or their income base changed. Some farmers did not have that adjustment.
            Because their resources adjusted, their need adjusted.
            Some are still over awarded but the over award cannot be more than what the original amount provided was. Does that help?

            Comment


              #7
              I think I got it.

              '=IF(I7<0,F7,IF(F7-I7<0,0,F7-I7))

              Comment


                #8
                Originally posted by Dusty Britches View Post
                I think I got it.

                '=IF(I7<0,F7,IF(F7-I7<0,0,F7-I7))
                You are on the right track. An embedded if/then/else statement is required.

                Comment


                  #9
                  Is that what that is?

                  Comment


                    #10
                    Originally posted by Dusty Britches View Post
                    Is that what that is?
                    Technically it’s called a “nested if function”

                    …just saying so you can google the correct terminology

                    Comment


                      #11
                      I love excel! Wish I could understand the formulas

                      Comment


                        #12
                        so write this formula.

                        If Joe had 2 chicken's and Ed had 4 horse. How long would it take Billy Bob's wife to cook dinner.

                        That is how little I understand what they wrote above.

                        Comment


                          #13
                          Originally posted by StrayDog View Post
                          Technically it’s called a “nested if function”

                          …just saying so you can google the correct terminology
                          That is what I am saying - the formula I wrote is a nested function. And this is a IF-THEN-ELSE statement.

                          Comment


                            #14
                            Originally posted by Stan R View Post
                            so write this formula.

                            If Joe had 2 chicken's and Ed had 4 horse. How long would it take Billy Bob's wife to cook dinner.

                            That is how little I understand what they wrote above.
                            It would take her 30 minutes because that's how long it takes to fry everything!

                            Comment

                            Working...
                            X