Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    155
    Thanks
    34
    Thanked 2 Times in 1 Post

    Percent formulas

    I have a Variable in Cell A1, in this case it's 20
    The Variable is to adjust a percent increase or decrease of a fixed amount

    If my fixed value in cell B1 is 10 and I want to decrease it by 20% in cell C1, currently I am getting 2
    So I have to use another formula to subtract 2 from 10, and I get 8

    What is the formula to make the value in C1 8 instead of 2


    I also need the reverse formula to increase it in another cell range by that same variable in A1
    So 10 becomes 12 if the increase is 20%

    That's the first phase, next I'll show a specific round up or round down requirement that is rather complex to.

    Thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    XP,

    This should do the trick:
    excelpct.JPG
    Note: this assumes you have 20 and not .20 in A1. HTH

    FYI: if you change the 20 to -20 it will result in 12.
    Or if you want it a little more intuitive change the formula to
    =B1*((100+A1)/100)
    now a positive number in A1 will INCREASE the value and a negative number will DECREASE the value.
    Last edited by RetiredGeek; 2013-11-03 at 09:40.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 169 Times in 165 Posts
    ..or why not enter the percent directly in cell [A1] as a percent????
    ..select cell [A1] and then just type 20% and press [Enter]

    ..then the formula in [C1] is..
    =B1*(1-A1)

    zeddy
    Attached Images Attached Images

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,140
    Thanks
    39
    Thanked 212 Times in 198 Posts
    Perhaps changing the formula to:
    =B1*(1+A1)
    This would allow for 20% to increase 10 to 12 while -20% to decrease 10 to 8 instead of the other way around.

  6. #5
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    155
    Thanks
    34
    Thanked 2 Times in 1 Post
    Ok, the above makes sense, but I got the idea from a code that was made for me ages ago for another program, ( long story )

    Within the code's user settings in the "Global Variables" section it has:
    const("PRICE_DEFICIT_PERCENT",varInteger,20)
    const("BASE_INCREASE_PERCENT",varInteger,20)
    Further down the entire "script" when the variables are required
    the formula is within the scripting, now I see how the - works

    Re: If my fixed value in cell B1 is 10 and I want to decrease it by 20% in cell C1, currently I am getting 2
    dec_prices[i-1] = raw_prices[i-1] * (1 - (PRICE_DEFICIT_PERCENT/100))
    and to increase by the variable if it's 20 the formula withing the code is:
    if Store.ReadValue("NewTarget",new_target) < 0 then new_target = DEFAULT_TARGET end if
    base_target = new_target * (1 + (TARGET_INCREASE_PERCENT / 100))
    As you guys will find, the formula are contained within these snippets of code, which is what I have to replicate in Excel using formula.

    When there once was a print-out, it would show the calculated sum, example
    10 being 8
    or
    10 being 12, whatever the case may be.
    Last edited by XPDiHard; 2013-11-03 at 17:45. Reason: ONE WORD ERROR

  7. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,067
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Maud's post is the simplest method:

    =B1*(1+A1)
    This would allow for 20% to increase 10 to 12 while -20% to decrease 10 to 8 instead of the other way around.

    If you didn't write the %, then similar to your old program you would have to take the 20 (or -20) and divide it by 100.

    =B1*(1+A1/100)

  8. #7
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    155
    Thanks
    34
    Thanked 2 Times in 1 Post
    OK,
    I have experimented, I am slightly overwhelmed, there is so much more to this "paradox", because there is another Function, it's part of process/paradox.

    However, I have uploaded a workbook showing a draft and some further formula that may require tweaking.
    It's all Percent and the function is a RoundUp RoundDown within the process.

    First:
    The Yellow in Column C are the Variables.
    The Red in Column C is a calculation of a Variable and the Green in Column C is the Sum of the 2
    I am not sure if it's correct way when the numbers become larger.

    Second:
    This is a "brake" if it goes to negative.
    It is meant to reside in H8 to calculate between Cell H7 and C9
    value in H8 ought to show in this case 80 % of 30 (we know 80 % of 100 = 80 )
    I think 80 % of 30 is 24 ?

    Third
    This is a tricky one.
    It's the roundup or down function that I hope can be a formula

    It has to relate to the Value in H4 H5 and H6, so the formula for each ought to be in I4 I5 and I6


    This is the function, within the function are other variables that I adjust
    '************************************************* ******************
    ' this function designed to round the spend value to the nearest .50
    half spends are allowed
    '************************************************* ******************
    function RoundSpend(spend)
    dim x
    x = Frac(spend)
    if (x < 0.25) then x = 0 end if
    if (x >= 0.25) and (x < 0.75) then x = 1 end if
    if (x >= 0.75) then x = 1 end if
    RoundSpend = Int(spend) + x
    end function
    -------------,
    There is more later.

    Thanks
    Attached Files Attached Files

  9. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,067
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Based on your information, I believe H8 should be: =H7*C9/100

    Why is I4 rounding DOWN or truncating? Shouldn't I4, I5, I6 be 10, 11, 9 respectively?

    If so, I4 formula would be: =round(H4,0)

    and fill that down.

  10. #9
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,140
    Thanks
    39
    Thanked 212 Times in 198 Posts
    XP,
    The formulas for I4, I5, I6 should be:
    I4 =ROUNDUP(H4/5,1)*5 yields 10
    I5 =ROUNDUP(H5/5,1)*5 yields 11
    I6 =ROUNDUP(H6/5,1)*5 yields 9.5

    This will round them up to the nearest .5 increment

    Maud
    Last edited by Maudibe; 2013-11-03 at 22:52.

  11. #10
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    155
    Thanks
    34
    Thanked 2 Times in 1 Post
    kweaver
    Based on your information, I believe H8 should be: =H7*C9/100
    The formula is to go in Cell H9 ( H8 is simply a Title)
    The Variable, in this case is 80, means 80 % of X
    X = Value in Cell H7
    When I use your formula I get 24, that is correct
    Thanks.

    maud,

    yes you are correct in using the round function in excel.

    But there has to be the option of tweaking and not rely on Excel's standard functions.
    I think it's a UDF.

    The current settings in the Round function allows the user if they choose to go up or down specifically.
    The reason for this, is the values in cell ranges E4 - E6 vary constantly, therefore varying the value in Cell ranges I4 to I7
    The current values in I4 to I7, I put them there myself to show the expected value IF there was a formula/UDF in those ranges.
    But, as you will note, without a UDF, in Cell I6 I get 9.5
    That's OK it is the correct answer, but with a UDF I have the option of setting it to go to
    either down to 9 or 10, eliminating the .5

    It was once used when a "buy" was incremented in .5 values, $0.50 cents.
    It's whole number/unit 1 or 2, not 1.5
    Last edited by XPDiHard; 2013-11-03 at 23:36.

  12. #11
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,140
    Thanks
    39
    Thanked 212 Times in 198 Posts
    XP,

    Round will also work to round up or down in increments of .5 and is equivalent to your UDF

    Cell I4 =ROUND(H4/5,1)*5

    You can control the rounding by using another cell as the control value. Consider the formula:

    Cell I4 =ROUND(H4/M1,1)*M1 Put an increment in cell M1 to control the rounding
    ex if cell H4=9.4. Place a 5 into M1 and H4 rounds to 9.5. Place a 10 in M1 and it will round to 9.
    Last edited by Maudibe; 2013-11-04 at 16:14.

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2013-11-04)

  14. #12
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    155
    Thanks
    34
    Thanked 2 Times in 1 Post
    SOLVED

    Thanks maud, it works better than expected, I can increase the 10 to 20 or 30 even 100, accidental discovery type of thing, some options in the "strategy" to look at later.

    Thanks.


    The final scenario, I will have to start another thread, will be the same workbook, but sheet 2.

    For the interim, it has to do with the "DEFAULT SPEND" and it's relative formula.
    Currently the formula we been dealing with, is slanted to spend the entire amount, in this case for example it's 20.
    It's OK for low priced "buys".

    There is a "reversal" where it's "make a profit of 20". This will change/lower and or the current spend amounts, Celll range H4 - H5.

    The reversal "safer" because if the spend amount is too high due to one of the buy amounts being too low, a limit can be implemented via a variable percent scale.
    ( if total buy amount is greater than Var ~ 80% of Target Profit amount, then don't buy, type of thing - another example-how much for a dozen eggs, can I get better than a baker's dozen ?)

    Will explain later in further detail with examples.

  15. #13
    New Lounger
    Join Date
    Apr 2013
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Am I missing something here? For many years I rounded exam marks to the nearest.5 just using the MOD function. If I remember rightly it was simply MOD(x+.5). Being old school I did frequent manual checks and never found any errors except when I had mis-entered the original mark!

  16. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    I am not sure what you are missing in the discussion, but you are missing the divisor in your formula.

    I am not sure what you are recalling, bBut mod(x+.5) is not a valid formula in excel without the required divisor: you need a number the you divide by to get the modulus (remainder)...

    Steve
    Perhaps you are thinking of:
    =INT(x/.5+0.5)*.5
    That is the way I recall without rounding...
    Last edited by sdckapr; 2013-11-09 at 06:49.

  17. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,212
    Thanks
    14
    Thanked 338 Times in 331 Posts
    steve
    Last edited by sdckapr; 2013-11-09 at 06:53.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •