[Excel]: Use If-Then-Else Statement to return %

Forums › Desktop computing › [Excel]: Use If-Then-Else Statement to return % - 3 variables?

baino88

4 posts

Wannabe Geek

#107359 10-Aug-2012 09:04

Morning GZ!

Sorry for the rookie question... Trying to get a resource allocation plan together. What I've got is several variables.

1st Variable: Type. Options = Project, Pilot
2nd Variable: Size. Options = Small , Medium, Large
3rd Variable: Phase. Options = Design, Build, Test

What I want is an excel rule that will look at the 3 variables and return a %.

E.g. A Project, which is Large and in Design Phase = 40%
A Project, which is Large and in Build Phase = 30%

Would love some help!

If you need anymore info, let me know!

sampler

446 posts

Ultimate Geek

ID Verified

Trusted

Lifetime subscriber

#670408 10-Aug-2012 09:23

So do you have a weighting for each variable ? or it it a weighting based on the combo of each variable ?

Are you trying to show the total value of each item ? or which has a greater need ?

Im not sure a percentage score is what you should be aiming for, maybe just a total number value. highest value = largest project closest to being completed ?

Cheers

Lee

baino88

4 posts

Wannabe Geek

#670413 10-Aug-2012 09:38

Hi Lee,

Is for a resource plan.. I.e. A Large Project in Design Phase will use

Weighting is based on the combo of each variable.

I think I'm trying to show the total value of each item?

Hopefully attached might help?

USER ERRORS x10 = http://tinypic.com/r/2qn63xw/6

sampler

446 posts

Ultimate Geek

ID Verified

Trusted

Lifetime subscriber

#670457 10-Aug-2012 10:09

Hi

Still not sure % really means anything as such.

Again first you need to work out what each item could be worth ...

Then a "simple" if statement could be used (looks yuck but works...)

(need to work out how to upload images into posts ... crazy ...)

Cheers

Lee

baino88

4 posts

Wannabe Geek

#670470 10-Aug-2012 10:27

You fine sir have earned yourself a beer!!

Many thanks for your help.

have a good one.

sampler

446 posts

Ultimate Geek

ID Verified

Trusted

Lifetime subscriber

#670480 10-Aug-2012 11:00

Might just need todo that ...

Glad I could help or at least push you in the right direction.

Would love to see the final weightings or solution you have come up with.

Cheers

Lee

D1023319

524 posts

Ultimate Geek

ID Verified

#671353 12-Aug-2012 11:13

Hi - another idea for your consider.

Little late but while the "if" function works - it can get messy and it has a limited number of nestings and gets very long or you have to break it up into multiple columns

While I am only a beginner myself - i'd suggest creating a VBA function

in the following example with your required inputs I created a function Wcalc with 3 inputs

Gives you a alt anyway

https://cdn.geekzone.co.nz/imagessubs/6e4a159f8d904168539dd43769948e70.jpg

Kyanar

4089 posts

Uber Geek

ID Verified

Trusted

#671875 13-Aug-2012 16:14

Personally, I'd stick the weightings into a seperate worksheet, and use VLOOKUP to extract the values. Basically VLOOKUP scans a table vertically, and returns the value in the nth column for the search text in the first column.