Percent formulas

Topic

New Reply

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

Reply | Quote

Replies

XP,

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

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
[noparse]=B1*((100+A1)/100)[/noparse]
now a positive number in A1 will INCREASE the value and a negative number will DECREASE the value.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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) and (x = 0.75) then x = 1 end if
RoundSpend = Int(spend) + x
end function

————-,
There is more later.

Thanks

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

steve

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

Thanks sdckapr, that is more like it , now you mention it the INT bit comes leaping back. I shall have to dig back into history now , I clearly can’t rely on memory! My guess is INT(x+.5) which should meet the convention of rounding .5 upwards.

Reply | Quote

Yes, Int(x+.5) is the method to round to the nearest integer, when there is no round function. But it does not round to the nearest half, but to the nearest integer.

[INT essentially “truncates” the decimal portion, so 1.5-1.99 would turn to 1, adding the 0.5 turns those values to numbers 2-2.49 so when they are truncated, the effects are to round the original number upwards to the nearest integer, not truncated]

Steve

Reply | Quote

Thanks again,
there is an update to this question, just waiting on some puter bits to sort out this old DOS program cos the graphics of that program don’t seem work on XP.
It’s where the actual resulting calculation ought to appear, the rest of that old program works fine.
Once I see that program’s result/Sums, then I can make a comparison with excel
I think I may have to install DOS 6 as the operating system exclusively.

Reply | Quote