Prev: speeding up calculation: replacing array formula with database function?
Next: Why won't text show in SOME cells?
From: Glenn on 7 Apr 2010 13:20
ewillig wrote:
> Rows = projects and Columns = components needed for this project. I am
> looking to make 2 calculations on each project. The first is the overall
> time required for this project based upon the components required for this
> project and the second is the balance of time required to complete it.
>
> In row 2 I have a series of base values - one each for 40 columns
> (components) starting at column M
> In left most column (column L) I have a multiplier picklist (1,2, or 3)
> created using data validation
> In each row after row 2 and to the right of column L, I have another
> picklist of (X, R, P, C where X is chosen if this component is not needed for
> this project; R indicated a required component; P indicates a required
> component which is already in process of being completed; and C indicated a
> component which is required that has already been completed.
>
> At the far right, after our 40 columns representing the 40 possible project
> components, I have 2 more columns. The first, column BA, is where I am
> looking to total the amount time required for this project (in hours or
> minutes) based upon the components needed*the base variable for that
> component (row 2)*difficulty of that project (column L). In the second
> column, column BB, I am looking for a similiar calcualtion but this one
> totals the balance of the time needed to complete this project (not the total
> time but the time required to complete the balance of the components - not
> started or already completed).
>
> I know that I can create custom functions for each of the 40 columns and
> combine them in a formula but that is both time consuming and cumbersome. I
> am hoping there is a more elegant formula that can be used.
>
> Thank you for your time and consideration.
>


In column BA:

=SUMPRODUCT((M3:AZ3<>"X")*($M$2:$AZ$2*$L$3))

In column BB:

=SUMPRODUCT((M3:AZ3="R")*($M$2:$AZ$2*$L$3))
 | 
Pages: 1
Prev: speeding up calculation: replacing array formula with database function?
Next: Why won't text show in SOME cells?