excel - Sumproduct dates -

-

i have spreadsheet worksheet called “iso procedure master list” date procedure requested in column g (format m/dd/yyyy).

in column h date procedure completed entered. column h uses same date format may contain blanks (procedure not completed yet).

there 67 rows of information in spreadsheet span 2011 2015 , number continue grow.

question, average time (in days) took procedure request (column g) completion of procedure (column h) of procedures requested in given year.

in other words average (in days) time took complete procedures in given year.

this answer in cell c34 in same workbook different worksheet called “iso matrix”. (this information not matter thought add in case)

i have tried several sumproduct variations no success.

assumptions:

  1. dates in columns g , h dates, not text string,
  2. data start in row 2, using row 1 headings.
  3. for convenience, assume have named ranges wish average requestdate , completedate
  4. you wish ommit rows procedure not finished average calculation.

after naming ranges, input following formula c34 on "iso matrix" tab:

=average(if(completedate<>"";completedate-requestdate))

this array formula, when pressing enter, remember press ctrl+shift+enter instead. when highlighting cell, this:

{=average(if(completedate<>"";completedate-requestdate))}

note: language pack uses ; formula separator, if language uses , instead, change formula to:

=average(if(completedate<>"",completedate-requestdate))

edit: so, didn't catch needed average year. in line recent formula, update suit request.

=average(if(completedate<>"";if(year(requestdate)=a15;completedate-requestdate)))

where a15 holds year interested in. tried , in first if instead of 2 ifs, did not seem work. mind you, might still need change ; , , need ctrl+shift+enter one.

you add code below, handle case test year not present in list:

=iferror(average(if(completedate<>"";if(year(requestdate)=a15;completedate-requestdate)));"no such year")

...or that


Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more