Previous Topic Next Topic

Multi percentage Calculation

Closed
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Nov 27, 2014 at 08:20 AM
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 - Dec 4, 2014 at 08:46 AM
Hi all, Good day!!

Need help in Excel formulas.

I'm creating a workbook to counting a value in multi percentage and multi level.

Here I attached my print screen sample workbook.



I need to set when I select TYPE in list then the calculation auto read the percentage in need.

e.g.:

when the column D type is " B " so the percentage is 1.2% then column F formula help me count the value like amount in 300 then colomn F showing 3.60 which get from 300 x 1.2% and colomn G & H is 0 value. If the type change to M then the colomn F & H become 0 value and colomn G count 300 x 1.7% value.

I also create a table for the TYPE list.


Thank you.


1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 28, 2014 at 10:11 AM
Most easy approach would be to to have only one column for %age

in that you can use formula as
=IF(OR(D1="", E1=""), "", E1 *VLOOKUP(D1,L:M,2,FALSE))

How ever, if you must have all those columns, then you can use similary vlookup, with error handling or use IF statement for each cell
0
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Nov 29, 2014 at 06:07 AM
Thanks Rizvisa1 :)

the formulas was very helpful in my other workbook :)

but in this workbook, I need to classify which the percentage had been charge by the type when I showing to my boss.

Do have others formulas can make it??

thank you :)
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018
Dec 2, 2014 at 05:56 AM
As I said you can use similar approach for each cell as

=IF(OR(D1="", E1=""), "", IF(ISERROR(VLOOKUP(D1,L:L,1,FALSE)), "", E1 *VLOOKUP(D1,L:M,2,FALSE)))

Formula is saying
1. If either D1 or E1 is blank, then show blank
2. If the value in cell D1 is not found in column L, then an error would happen. This is checked. If error occurred, then show blank
3. use the formula to calculate
0
Winndixie Posts 20 Registration date Monday March 10, 2014 Status Member Last seen September 28, 2018 > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Dec 4, 2014 at 08:46 AM
Dear Rizvisa1,

Have a Nice day !!

the formula is work...but my column D & E will not be blank because I need to fill the type to classify the percentage charges... so every time I change the type then I need the a formula to help me auto run the percentage that charge

e.g

- when every time changing the type .... percentage column auto running the percentage

-just like when change to B .... then column 1.2% will showing the value and others showing " - " or blank. If change to M then showing at the column 1.7% then 1.2% and 2.5% column auto showing "-" or blank.

is it possible to set a formula like this??? many times I had tried but still erro ...


Thanks & regards :)
0

Similar discussions

LG LED (Webos) Multi-view
Asmare -
dwebb -

3 responses
Percentage Work
Winndixie -
RayH -

6 responses
Percentage calculation
dany -
m.thenmozhi -

2 responses
calculating fuel surcharge
Nancy -
xpcman -

1 response
HOW TO CALCULATE AGGREGATE OF STUDENT'S MARKS
peterappiahkubi -
Wofanat -

2 responses