Change by Amount
Hello All,
I am looking for a quick VBA to do this. Whenever users put a percentage number in cell F1, then it increases or decreases values (by a percentage) in column “Dollar” and puts the corresponding result in “changedDollar” column
For example: if user put 4 in cell F1, then F5 = 45 + 45*.04 =46.8
For example: if user put - 4 in cell F1, then F5 = 45 -45*.04 = 43.2
If the total after percentage decrease, becomes a negative number, then that total becomes zero
For example if the percentage decrease is -1.5, then
F5 = 45-(45*1.5) = -22.5
This is not allowed as changedDollar CANT be negative. Hence in that case, changedDollar = 0
F5 = 45-(45*1.5) becomes 0
changeByPercent.xlsx
I am looking for a quick VBA to do this. Whenever users put a percentage number in cell F1, then it increases or decreases values (by a percentage) in column “Dollar” and puts the corresponding result in “changedDollar” column
For example: if user put 4 in cell F1, then F5 = 45 + 45*.04 =46.8
For example: if user put - 4 in cell F1, then F5 = 45 -45*.04 = 43.2
If the total after percentage decrease, becomes a negative number, then that total becomes zero
For example if the percentage decrease is -1.5, then
F5 = 45-(45*1.5) = -22.5
This is not allowed as changedDollar CANT be negative. Hence in that case, changedDollar = 0
F5 = 45-(45*1.5) becomes 0
changeByPercent.xlsx
ASKER
Also this change will register in cells that are visible only, not hidden cell rows
Hello,
could you please clarify one thing for me. Your example lsits a value of -1.5, yet this is not a percentage in your example. It would be my understanding that it should read:
F5 = 45-(45*0.015) = 44.325
The only time a value could be negative would be when you enter in a value less than -100.
Is there any reason you want to use VBA? YOu could just use calculations rather than VBA Code.
I have attached an Excel file using a simple if (for the cases where F1 is less than 100) plus a calculation for the total amount for each column.
changeByPercent.xlsx
could you please clarify one thing for me. Your example lsits a value of -1.5, yet this is not a percentage in your example. It would be my understanding that it should read:
F5 = 45-(45*0.015) = 44.325
The only time a value could be negative would be when you enter in a value less than -100.
Is there any reason you want to use VBA? YOu could just use calculations rather than VBA Code.
I have attached an Excel file using a simple if (for the cases where F1 is less than 100) plus a calculation for the total amount for each column.
changeByPercent.xlsx
ASKER
Hello Harsem,
thank you for your suggestion :)
Since the table that you are seeing would actually be a much bigger table with filter check boxes above and users would be filtering that huge table to thier selected rows only and then doing the percent changes on those rows only ...that's why I asked for VBA or whichever way to do this calculation Only to those visible rows, not hidden rows....
let me know if that makes sense...
thank you
thank you for your suggestion :)
Since the table that you are seeing would actually be a much bigger table with filter check boxes above and users would be filtering that huge table to thier selected rows only and then doing the percent changes on those rows only ...that's why I asked for VBA or whichever way to do this calculation Only to those visible rows, not hidden rows....
let me know if that makes sense...
thank you
ASKER
Harsem,
Also the calculated numbers need to be then in xlvalues, not remain as formulas
Also the calculated numbers need to be then in xlvalues, not remain as formulas
ASKER
Any experts are Welcome here :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Harsem,
That works like a Charm :)
Thanks
That works like a Charm :)
Thanks
ASKER