Multi percentage Calculation

[Closed]
Report
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
-
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
-
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 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

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 :)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768 >
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018

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
Posts
23
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 28, 2018
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

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 :)