+ Reply to Thread
Results 1 to 19 of 19

Reoccure Character

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Question Reoccure Character

    Hi! Everybody,

    I am from India, New Delhi.

    We Indian celebrating Diwali, wish you all a happy diwali.

    I have on problem of array that i want total present i.e. "P" comes automatically if i put no. of days say 16 so total 16 Ps will be increment cell by cell.

    I attach file please help.
    Attached Files Attached Files
    Last edited by koshish; 10-23-2014 at 11:59 PM.

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Excel Formula & complex equations

    Hi, happy Diwali,

    I don't get what you want clearly, but I guess this is what you want :

    The value of cell BN20 (the numbers of expected P) is 15

    The formula at cell D20 is :
    Please Login or Register  to view this content.
    and copy to right until cell BM20
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Formula & complex equations

    Hi koshish and welcome to the forum, and thanks for the good wishes


    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Re: Excel Formula & complex equations

    Yes, Yes Ooh in one go you sense my question.......

    Kindly help me in writing the formula..... it will help save my time.....

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Excel Formula & complex equations

    Quote Originally Posted by koshish View Post
    Yes, Yes Ooh in one go you sense my question.......

    Kindly help me in writing the formula..... it will help save my time.....
    Glad to help, and don't forget to mark this thread as Solved

    Regards

  6. #6
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Re: Reoccure Character

    Can you please equally distribute 150 hours.... within 31 days....

    Hope I got the complete solution.....
    Attached Files Attached Files

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Reoccure Character

    Formula at cell D20 :

    Please Login or Register  to view this content.
    Hope I give the complete solution.....

  8. #8
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Question Re: Reoccure Character

    Hi!

    I am still small work needs to be done just check BP it is not matching....

    OT will be filled if P is there.
    Attached Files Attached Files

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Reoccure Character

    Of course, since you do rounding A/B, then if the rounded number re-multiply with B again, you won't get A.

    If you want to avoid fractions, then this is the formula, it won't yield a same number for all items.

    Formula at cell D20 :

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Question Re: Reoccure Character

    Not yet we are near to achieving final outcome.

    First of all forget C col.

    Second still not match BO:BP, kindly see......
    Attached Files Attached Files

  11. #11
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Reoccure Character

    Quote Originally Posted by koshish View Post
    Not yet we are near to achieving final outcome.

    First of all forget C col.

    Second still not match BO:BP, kindly see......
    Dear Koshish,

    First of all, please be noticed that people here willing to help just for materials covered in the thread's title, so not all the job will be processed by the helper. Since you are the one who understand the rule/logic to calculate the OT etc, so you must do it by yourself. I don't even know what A, S, P, H, OT symbols mean. I will help you at the part that you describe in first post, that is : " I have on problem of array that i want total present i.e. "P" comes automatically if i put no. of days say 16 so total 16 Ps will be increment cell by cell".

    As for the second question, did you ever try the new formula I gave you in post #9 ? Put that formula on cell D20 and copy it to the right until cell BM20, you will get the same result for cell BO20 and BP20.

  12. #12
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Smile Re: Reoccure Character

    Dear Sir,
    Thank you very much, I enjoy your inputs.....

    Sir, as suggested I copied formula in desired range, Sir unmatched the total because of.

    If "P"=Present is in cell, "OT"=Overtime contain hour else zero.

    If "P" is not in cell no "OT" hour.

    Hope you enjoy ......
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Re: Reoccure Character

    Becasue if I prepare 880 people records it will be the great solution.....

  14. #14
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Reoccure Character

    Quote Originally Posted by koshish View Post
    Dear Sir,
    Thank you very much, I enjoy your inputs.....

    Sir, as suggested I copied formula in desired range, Sir unmatched the total because of.

    If "P"=Present is in cell, "OT"=Overtime contain hour else zero.

    If "P" is not in cell no "OT" hour.

    Hope you enjoy ......
    Quote Originally Posted by koshish View Post
    Becasue if I prepare 880 people records it will be the great solution.....
    You should mention that these generated OTs have dependency with generated "P"s.

    Here are the new formulas, since you mention this would have 800 records, so we need speed up the calculation by adding helper columns (so, there is no unnecessary same calculation is performed). You can also look at the attached file.

    Formula at BP20 :
    Please Login or Register  to view this content.
    Formula at BQ20 :
    Please Login or Register  to view this content.
    Formula at D20 :
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Re: Reoccure Character

    Dear,
    My wish is listened.....

    Complete solution......

    Happy with intact solution.....

    Thank you so much....

  16. #16
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Smile Re: Reoccure Character

    Dear,
    Not yet intact, please help.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Smile Re: Reoccure Character

    Dear,

    Can you incorporate in this enclosed file........
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Re: Reoccure Character

    Any body can response on my previous two file issue overtime should match...

  19. #19
    Registered User
    Join Date
    10-22-2014
    Location
    New Delhi
    MS-Off Ver
    2007
    Posts
    22

    Smile Re: Reoccure Character

    Hi!
    Upload two files one file namely EP1044821(C).XLSX has a formula in row B20 onwards, and second file namely EP1044821(C)(Rev).XLSX will be the final output file where the formula actually placed.

    Required in dates columns the attendance should marked as "Ps"=Present increments from row cell G12 till BP232 (file name:EF0144821(C)(Rev).xls) under date column (1 to 31) only as per numbers in BT12 till BT232.

    The total sum of P’s in Columns BT & BS should EQUALLY match.

    Next, come to columns named as “OT”=Overtime, that will equally divided from row cell G12 till BP232 (file name EF0144821(C)(Rev).xls) as per figures in columns BU12 till BU232, under OT column (1 to 31) only and match total numbers in BR12 till BR232. One more condition RELATED TO OT HOURS, if date columns marked “P” then only OT columns will have figure else say ZERO.

    All help would be greatly appreciated.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 03-25-2014, 01:25 PM
  2. [SOLVED] Formula with two equations
    By OverKnight in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 10:46 PM
  3. Complex Excel Formula Required for Obtaining Values based on Column & Row
    By adeel726 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 04:36 PM
  4. A formula that is too complex for Excel?
    By trex005 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-24-2006, 12:30 PM
  5. [SOLVED] complex excel formula Array how do I convert it to a vba Function
    By Rob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-10-2006, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1