Excel

Excel

[[WebService]] - it is possible to call a web service. Here is an example

I had a spreadsheet with several columns

  • J - a person who will do the task
  • E - an estimate of time to perform something
  • K - the phase that the task is going to do something
  • L - column l was hidden and contained values like = j1 & k1 - it was used for lookup of how much a person was working on a specific task.

I wanted to summerize the tasks to come up with

  • who is working on the project.
  • at which phase is that person needed for that project
  • how much resources will be needed. (Both all resources, and resource per phase)

The Person

1
b423=INDEX(J$3:$J408, MATCH(0, COUNTIF($B$422:$B422,J$3:$J408),0))

where

  • J$3:$J408 - person assigned to the task
  • $B$422:$B422 - peopel we have evaluated so far
  • J$3:$J408 - person assigned to the task

How much time is needed for a resource

e390=SUMIF($J$3:$J374,$J390,$E$3:$E374)

where

  • $j$3 - $j390 - person assigned to the task ** Range of critera data **
  • $j390 - person who we are looking at ** Criteria value - can be “x”, or “=x”, or “>421”, etc…
  • $e$3-$e$374 - estimate of time - ** These contain the numbers that will be added ** ** if this argument is not used, then it will use the first range instead**

+++ how much time was spent on a phase

e423=SUMIF($K$3:K$374,$A423,E$3:E$374)

where

  • k3-k374 - phase of each task

  • $a423 - phase we are working on

  • e3-e384 - estimate of the time

    =SUMIF($L$3:$L$374,$A$421 & $B423,E$3:E$374)

where

  • $L$3:$L$374 - concat of phase, person performing the task
  • $A$421 & $B423 - person we are examining
  • E$3:E$374 - time spent by that person task

Functions

  • =SUMIF (Range, Criteria, [SumRange])

  • =CountIf (Range, Criteria)

  • =Index(array, row_num, [column_num]) Array form

  • =index(reference, rownum, [column_num], [area_num]) reference form

  • =match(lookup_value, lookup_array, [match_type])

    matchtype=-1, 0, 1 (default 1)
    1 - largest value <= value in lookup value
    0 - first value = lookup value
    -1 - smallest value that is >= lookup value

Dont forget Control / Shift / Enter when recording calculations.

References