# Posts by Robert Mika

• ## After Copy Past shows Error. (#VALUE!)

Re: After Copy Past shows Error. (#VALUE!)

Not sure how you are getting one with the VALUE but one suggestion for cell DE15 and others in summary
You can use
=SUMIF(\$E\$8:\$CW\$8,DE\$8,\$E15:\$CW15)

• ## Find values using Offset, Index, Max

Re: Find values using Offset, Index, Max

Quote from kleo;680234

Thank you very much, Robert. It works.
Kleo

You are welcome.

• ## Index,Match Large

Re: Index,Match Large

Quote from loknath;680235

Hi Robert, The solution you provided is awesome. Thank you very much.

You are welcome

• ## Extract Only Numbers Using Formulas In Excel

Re: Extract Only Numbers Using Formulas In Excel

=NPV(-0.9,,IFERROR(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)%,""))
Confirm COntrol+Shift+Enter

(I hoped I did not breach the rules)

• ## Summing Whole Numbers Gives Non Whole Number Output

Re: Summing Whole Numbers Gives Non Whole Number Output

forum.ozgrid.com/index.php?attachment/55816/
You can change to general to get 0 but as you see in yellow cell if you will make futher calculation and add more decimal points you will still end up with 0.99999.....

• ## Index,Match Large

Re: Index,Match Large

J4(drag to J5)
=INDEX(\$A\$4:\$A\$11,SMALL(IF(\$B\$4:\$B\$11=\$I4,ROW(\$B\$4:\$B\$11)-ROW(\$B\$4)+1),ROWS(\$A\$1:A1)))
Confirm Control+shift+Enter

• ## find non null in column and move it to first row?

Re: find non null in column and move it to first row?

forum.ozgrid.com/index.php?attachment/55795/
Example for your first question attached.

Can you give an output example for the years with you question?

• ## find non null in column and move it to first row?

Re: find non null in column and move it to first row?

Try N2 drag down confirm Control+Shift+Enter
=IF(M2="","",INDEX(\$L\$2:\$L32,SMALL(IF(\$L\$2:\$L\$32<>"",ROW(\$L\$2:\$L\$32)-ROW(\$L\$2)+1),SUMPRODUCT(--(LEN(\$M\$2:M2)>0)))))

• ## find non null in column and move it to first row?

Re: find non null in column and move it to first row?

This coudldbe done by formula but it will take one additional column.

• ## Oldest date if unresolved=true

Re: Oldest date if unresolved=true

=MIN(IF(\$B\$2:\$B\$5="yes",\$A\$2:\$A\$5))
Confirm COntrol+Shift+Enter

• ## adding the same cell across multiple worksheets based on criteria on the worksheet

Re: adding the same cell across multiple worksheets based on criteria on the workshee

like:
=SUM(Sheet1:Sheet3!A1)

?

• ## Urgent!! Advanced formula filling

Re: Urgent!! Advanced formula filling

then in C9 drag down:
=IF(AND(C\$3>=INDIRECT("Sheet2!\$N"&ROW(\$A\$5)*ROW(A2)),C\$3<=INDIRECT("Sheet2!\$o"&ROW(\$A\$5)*ROW(A2)),INDIRECT("Sheet2!\$N"&ROW(\$A\$7)+ROW(A1)*5)=\$C\$1),1,0)

• ## Error returned (but not 100% of the time) when using IF with MATCH and INDEX

Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX

Try this confirm Control+Shift+Enter (in F5,F9...)
=IF(D5="","0",IF(INDEX(\$B\$5:\$B\$292,MATCH(ROUND((D5-1/24),10),ROUND(\$A\$5:\$A\$292,10),0))<((E5-(D5-1/24))+(D5>E5)),"Over","Under"))

With 15 digits after comma even ROUND/up will fail is applied to your criteria only.
Apply this to your second formula

• ## VLookup/Index to return multiple values

Re: VLookup/Index to return multiple values

Just to clarify.
Where (column,row?) user will be entering the NU ID?

• ## Calculate number of hours worked

Re: Calculate number of hours worked

=IF(C2<>"",(C2-B2+E2-D2)*24,(C2-B2)*24)
Format as General

• ## Calculate number of hours worked

Re: Calculate number of hours worked

Try:

F2 drag down
=IF(C2<>"", C2-B2+E2-D2,C2-B2)
format as h