Results 1 to 15 of 18
Thread: Percent formulas

20131103, 09:06 #1
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 146
 Thanks
 28
 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

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!
+ 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!

20131103, 09:35 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 5,826
 Thanks
 185
 Thanked 704 Times in 642 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; 20131103 at 09:40.

20131103, 15:08 #3
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,472
 Thanks
 22
 Thanked 167 Times in 163 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*(1A1)
zeddy

20131103, 15:34 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,012
 Thanks
 37
 Thanked 176 Times in 163 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.

20131103, 16:07 #5
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 146
 Thanks
 28
 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)
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[i1] = raw_prices[i1] * (1  (PRICE_DEFICIT_PERCENT/100))
if Store.ReadValue("NewTarget",new_target) < 0 then new_target = DEFAULT_TARGET end if
base_target = new_target * (1 + (TARGET_INCREASE_PERCENT / 100))
When there once was a printout, it would show the calculated sum, example
10 being 8
or
10 being 12, whatever the case may be.Last edited by XPDiHard; 20131103 at 17:45. Reason: ONE WORD ERROR

20131103, 19:35 #6
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,040
 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)

20131103, 21:00 #7
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 146
 Thanks
 28
 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

20131103, 22:29 #8
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,040
 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.

20131103, 22:50 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,012
 Thanks
 37
 Thanked 176 Times in 163 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
MaudLast edited by Maudibe; 20131103 at 22:52.

20131103, 23:24 #10
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 146
 Thanks
 28
 Thanked 2 Times in 1 Post
kweaver
Based on your information, I believe H8 should be: =H7*C9/100
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.5Last edited by XPDiHard; 20131103 at 23:36.

20131104, 15:51 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,012
 Thanks
 37
 Thanked 176 Times in 163 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; 20131104 at 16:14.

The Following User Says Thank You to Maudibe For This Useful Post:
XPDiHard (20131104)

20131104, 18:33 #12
 Join Date
 Nov 2011
 Location
 Australia
 Posts
 146
 Thanks
 28
 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 examplehow much for a dozen eggs, can I get better than a baker's dozen ?)
Will explain later in further detail with examples.

20131109, 05:58 #13
 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 misentered the original mark!

20131109, 06:39 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 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; 20131109 at 06:49.

20131109, 06:49 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 Posts
steve
Last edited by sdckapr; 20131109 at 06:53.