+ Reply to Thread
Results 1 to 7 of 7

Calculate based on highest values.

  1. #1
    Forum Contributor
    Join Date
    05-16-2010
    Location
    glasgow scothland
    MS-Off Ver
    Excel 2007
    Posts
    112

    Calculate based on highest values.

    Hello All,

    I have this file were in sheet1 there is 5 people for now.They are each given a list to place certain bids in.

    if for example sheet1 J3 is greater than K3 and if sheet2 numbers in F3 ,G3 exactly match these numbers then person 1 gets 3pts
    if j3 is greater than k3 but the numbers in F3,G3 are not the same then they get 1 point.

    if K3 and J3 are equal and match sheet2 numbers then they again get 3pts but again if they are the same (J3,K3) but different to sheet2 F3,G3 and sheet2 numbers are
    equal they get 1 point.
    The values in sheet1 won't be calculated until values in sheet2 cols F & G are filled in each week.

    I can do this in formula's but i won't be running this workbook and i know it will be messed up,so i think if this could run with vba then it can't be messed up.
    I know i can protect the sheet,but i would like to study a vba solution.

    I have attached a mocked up example.

    Thanks for any help with this.
    Attached Files Attached Files
    Last edited by mdshotgun; 06-28-2014 at 04:19 AM.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Calculate based on highest values.

    Please Login or Register  to view this content.
    assuming that the rows are the same for the sheet1 and sheet2

  3. #3
    Forum Contributor
    Join Date
    05-16-2010
    Location
    glasgow scothland
    MS-Off Ver
    Excel 2007
    Posts
    112

    Re: Calculate based on highest values.

    Hello rcm,

    Thanks for your efforts.
    I have done this with formula similar to yours,but i was hoping to get a macro solution.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Calculate based on highest values.

    Based on your "rules", I dont agree with some of your sample answers...

    J
    K
    L
    M
    2
    Person 1
    Total
    3
    45
    3
    1
    1
    4
    97
    98
    0
    0
    5
    16
    25
    1
    0
    6
    17
    73
    1
    0


    In M, i have my answers using this, copied down...
    =IF(J3>K3,IF(AND(J3=Sheet2!$F3,Sheet1!K3=Sheet2!$G3),3,1),0)

    if j3 is greater than k3 but the numbers in F3,G3 are not the same then they get 1 point.
    J5<K5, answer should be 0, you have 1
    J6<K6, answer should be 0, you have 1
    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

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Calculate based on highest values.

    OK, here goes the macro
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-16-2010
    Location
    glasgow scothland
    MS-Off Ver
    Excel 2007
    Posts
    112

    Re: Calculate based on highest values.

    Hello rcm,

    Thanks for your macro.


    Would you mind taking a little time to explain this line
    Please Login or Register  to view this content.
    Thanks.

  7. #7
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Calculate based on highest values.

    the formula offsets the column number (c) so for J=1 then c=10, for j=2 then c=14. c(j) "as a function of j" marks the first column for the person in question for all 5
    you could also say c=j*4+6

+ 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. Auto sort values from highest to lowest based off of value
    By qj67 in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 06-24-2014, 10:24 AM
  2. [SOLVED] Calculate highest value based on another value
    By aldousbri in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2012, 08:32 AM
  3. Summing Highest Values based on two columns
    By seashell in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2012, 07:53 AM
  4. Auto Sort Values From Highest To Lowest Based Off Of Value
    By CXP2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2011, 09:15 PM
  5. Formula to calculate highest values in a range
    By Dabooj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2008, 10:42 AM

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