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)
instead the SUM(,,,,)
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)
instead the SUM(,,,,)
Re: Find values using Offset, Index, Max
Quote from kleo;680234Thank you very much, Robert. It works.
Kleo
You are welcome.
Re: Index,Match Large
Quote from loknath;680235Hi Robert, The solution you provided is awesome. Thank you very much.
You are welcome
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)
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.....
Re: Summing Whole Numbers Gives Non Whole Number Output
This is know issue:
http://support.microsoft.com/kb/214118
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
Re: Indirect Formula Question
forum.ozgrid.com/index.php?attachment/55806/
Like this above?
in this case F10 is Cell_find
Re: Find values using Offset, Index, Max
If you need more than one return in sinle cell look here:
http://www.xl-central.com/concatenate-th…in-a-range.html
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?
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)))))
Re: find non null in column and move it to first row?
This coudldbe done by formula but it will take one additional column.
Re: Oldest date if unresolved=true
=MIN(IF($B$2:$B$5="yes",$A$2:$A$5))
Confirm COntrol+Shift+Enter
Re: Urgent!! Advanced formula filling
c8 your formula
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)
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
Re: VLookup/Index to return multiple values
Just to clarify.
Where (column,row?) user will be entering the NU ID?
Re: Calculate number of hours worked
=IF(C2<>"",(C2-B2+E2-D2)*24,(C2-B2)*24)
Format as General
Re: Calculate number of hours worked
Try:
F2 drag down
=IF(C2<>"", C2-B2+E2-D2,C2-B2)
format as h