+ Reply to Thread
Results 1 to 9 of 9

IF question for multiple pages

  1. #1
    Registered User
    Join Date
    04-18-2008
    Posts
    4

    IF question for multiple pages

    Hi,

    Hopefully this makes sense. I have a document that has lots of pages, the attached only has 3. The first is a summary which requires averages from cells on the other pages. Highlighted in green on the summary page are three different numbers each page could apply to (each subsequent page has a green cell that could be any one of the three values).

    Each page has multiple data entries (only one will be used for this question for simplicity). Highlighted in red is the number I need.

    Here is the tricky part, on the two pages (labeled 1,2) If the green cell = 1.25mm I need the average of the red cells on all sheets where the green cell equals 1.25mm to be in the appropriate red box on the summary page.

    However, the number in the green cell could be 1.50mm and I would need the average of the red cells on all pages where the green cell equals 1.50mm, and the same if the green cell equals 1.75mm.


    I hope this makes sense and is possible. Any assistance would be greatly appreciated, even if it is to say I can't do what I am trying to do

    Greg
    [email protected]
    Attached Files Attached Files

  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Hi clair_g, it would probably increase your number of views if you attached your sheet as compatible with previous excel versions.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Try this in C20

    Please Login or Register  to view this content.
    This in C22

    Please Login or Register  to view this content.
    This in C24

    Please Login or Register  to view this content.
    Portuga - you may find this useful http://support.microsoft.com/kb/925180
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Cool,

    Thanks oldchippy

  5. #5
    Registered User
    Join Date
    04-18-2008
    Posts
    4

    Thanks for the reply

    Portuga, sorry, the file was too large to upload as a 2003 document.

    oldchippy, thanks for the option, unfortunately it only seems to work if both of the referenced (green cells) on pages 1 and 2 are filled in with the same entry. I need it to be able to distinguish between all three options (1.25,1.5,1.75) and only include results from pages with the corresponding number.

    thanks,

    Greg

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    May be this in C20,

    =IF('1'!I2=1.25,'1'!E19,0)+IF('2'!I2=1.25,'2'!E19,0)

    Same in C22 and C24 adjust as necessary

  7. #7
    Registered User
    Join Date
    04-18-2008
    Posts
    4

    Almost :)

    Thanks oldchippy,
    Your last solution is really close. I need it to average the results from the red cells. So if both green cells on pages one and two happen to be the same (1.25 etc.) than it will not add the red cells together but provide the average of the two. I tried to do it by changing your formula but I can't seem to make it work.

    Thanks for all your help, if this works out it will save me and my coworkers hours and hours of time.

    Greg

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    =IF('1'!I2=1.25,'1'!E19,0)+IF('2'!I2=1.25,'2'!E19,0) /2

    Divide by the number of pages you are refering to

  9. #9
    Registered User
    Join Date
    04-18-2008
    Posts
    4
    Thanks,

    I will give it a try.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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