MrExcel.com - Your One Stop for Excel Tips & Solutions

Formula and Possible Macro Help

Posted by James Elliott on December 18, 2001 12:58 PM

Hi there! I am trying to write a formula where a total percentage is calculated from taking the status of six dropdown boxes (ex. if three dropdowns have the choice "completed" marked, then I want a column that I have marked "overall completion status" to say 50%)

Does anyone know how to write a formula for this or if I have to write a macro? I can email anyone the sheet to take a look at if they are interested.

Thanks for your help!

James

Posted by Aladin Akyurek on December 18, 2001 1:07 PM

James --

Assuming that your six dropdown boxes in A, say A2:A7, then you can use in the target cell:

=IF(COUNTIF(A2:A7,"completed")>=3,0.5,"")

Aladin

=========

Posted by Russell Hauf on December 18, 2001 1:32 PM

And if you want the percentage of completed, you can modify that to read:

=COUNTIF(A2:A7,"COMPLETED")/6

But if your dropdowns are actually combo boxes, it's a different story (and you should probably use data validation instead).

Hope this helps,

Russell --

Posted by James Elliott on December 18, 2001 1:35 PM

Aladin-

Thanks for your email! Unfortunately, I still can't get it to work. The cells I need to calculate are C5, D5, E5, F5, G5, & H5. I tried playing around with it, but had no luck. Do you think I can email you the sheet so you can see what I am trying to do? I am not trying to make you do it for me, but I need to know how to make it work. If you can, please email me at: jamesmelliott@yahoo.com

Thanks so much for your help!

James --

Posted by Aladin Akyurek on December 18, 2001 2:40 PM

Russell --

> And if you want the percentage of completed, you can modify that to read:

> =COUNTIF(A2:A7,"COMPLETED")/6

> But if your dropdowns are actually combo boxes, it's a different story

Bingo! James had not yet established Cell links. I rectified that in his workbook and used the following formula:

=COUNTIF(A3:F3,MATCH(G2,DDList,0))/6

where A3:F3 houses the ComboBoxes, G2 the status e.g., "Completed", an item that is selectible from each of the 6 ComboBoxes, and DDList the Input Range for the ComboBoxes consisting of

TBD
Completed
In progress
Not Started

> (and you should probably use data validation instead).

Added this advise which can be used with the simpler formula.

Aladin

=========== --