$$Excel-Macros$$ Formula required please
Dear experts, I am struggling in one formula steps, please help me out. please find the attachment for more information, please. Actaully i want a formula which gives me like R column, please forget about P, Q, R coloumn. P, Q are only helping column for me, so that i can elaborate you all the requirement. P and Q are from J and K coulumn. With warm regards, Rajan Sharma -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. Formula Required.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Formula required please
Some body can help me for creating the formula please With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 1:58 PM, Rajan sharma rajansharma9...@gmail.comwrote: Dear experts, I am struggling in one formula steps, please help me out. please find the attachment for more information, please. Actaully i want a formula which gives me like R column, please forget about P, Q, R coloumn. P, Q are only helping column for me, so that i can elaborate you all the requirement. P and Q are from J and K coulumn. With warm regards, Rajan Sharma -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Formula required please
Dear Premor sir, Exactly matching as per my requirement. Thanks a ton. even the formulas are long it gives my solution. With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 2:55 PM, De Premor d...@premor.net wrote: Hi Rajan, try this long formula on Cell S2 or Just see at attached workbook, i hope someone can give us a simple formula =IF(L2=INVESTMENT CONFIRMED,SUMPRODUCT((OFFSET($L$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))=INVESTMENT CONFIRMED)*OFFSET($K$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1)))/SUMPRODUCT((OFFSET($L$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))=INVESTMENT CONFIRMED)*OFFSET($J$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))),) On 26-08-2013 16:06, Rajan sharma wrote: Some body can help me for creating the formula please With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 1:58 PM, Rajan sharma rajansharma9...@gmail.comwrote: Dear experts, I am struggling in one formula steps, please help me out. please find the attachment for more information, please. Actaully i want a formula which gives me like R column, please forget about P, Q, R coloumn. P, Q are only helping column for me, so that i can elaborate you all the requirement. P and Q are from J and K coulumn. With warm regards, Rajan Sharma -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this
Re: $$Excel-Macros$$ Formula required please
Thanks Premor sir, Fantastic, Hats off to ur UDF.. thanks a ton. With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 4:40 PM, De Premor d...@premor.net wrote: Try this using UDF Function SUMPB(Amount As Range, Unit As Range, RngCriteria As Range, Criteria As String) Dim i As Long, FirstRow As Long, SumAmount As Double, SumUnit As Double FirstRow = 1 If RngCriteria.Cells(RngCriteria.Count, 1) = Criteria Then For i = 1 To RngCriteria.Count If RngCriteria.Cells(i, 1) = Then FirstRow = i + 1 Next For i = FirstRow To RngCriteria.Count If RngCriteria.Cells(i, 1) = Criteria Then SumAmount = SumAmount + Amount.Cells(i, 1) SumUnit = SumUnit + Unit.Cells(i, 1) End If Next SUMPB = SumAmount / SumUnit Else SUMPB = vbNullString End If End Function Then On Cell S2 type this Formula *=sumpb($K$2:K2,$J$2:J2,$L$2:L2,INVESTMENT CONFIRMED)* Pada 26/08/2013 17:35, Rajan sharma menulis: Dear Premor sir, Can your formula provided be done by Vba Macros. Actually i have many sheets to update your formula, so that it makes effect to all the sheets. With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 3:10 PM, Rajan sharma rajansharma9...@gmail.comwrote: Dear Premor sir, Exactly matching as per my requirement. Thanks a ton. even the formulas are long it gives my solution. With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 2:55 PM, De Premor d...@premor.net wrote: Hi Rajan, try this long formula on Cell S2 or Just see at attached workbook, i hope someone can give us a simple formula =IF(L2=INVESTMENT CONFIRMED,SUMPRODUCT((OFFSET($L$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))=INVESTMENT CONFIRMED)*OFFSET($K$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1)))/SUMPRODUCT((OFFSET($L$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))=INVESTMENT CONFIRMED)*OFFSET($J$1,LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),0,LARGE(ROW($L$1:L2)-LARGE(($L$1:L2=Status Description)*ROW($L$1:L2),1),1))),) On 26-08-2013 16:06, Rajan sharma wrote: Some body can help me for creating the formula please With warm regards, Rajan Sharma On Mon, Aug 26, 2013 at 1:58 PM, Rajan sharma rajansharma9...@gmail.com wrote: Dear experts, I am struggling in one formula steps, please help me out. please find the attachment for more information, please. Actaully i want a formula which gives me like R column, please forget about P, Q, R coloumn. P, Q are only helping column for me, so that i can elaborate you all the requirement. P and Q are from J and K coulumn. With warm regards, Rajan Sharma -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post
