+ Reply to Thread
Results 1 to 17 of 17

How to take data from multiple cells that are not together?

  1. #1
    Registered User
    Join Date
    03-03-2007
    Posts
    14

    How to take data from multiple cells that are not together?

    Hiya,

    Prob confused everyone with that title!

    I am trying to ask excel to look at the information that appears in certain cells,add it together and then divide it by another cells data. The problem is the info to add up is not all together so i cant say, for example, =SUM(A1:A5). The cells I need info from are, however, in a pattern of every 8 rows. Example:

    C5,C13,C21,C29. Rather than have to type every cell into the formula I am sure you clever people have an easier solution!!

    Thanks again

    Rob

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    This might work for you:

    =SUMPRODUCT((MOD(ROW($C$1:$C$500),8)=5)*($C$1:$C$500))

    Change your range as needed, C1:C500 is an example. This will sum every cell in the column where the row() value divided by 8 leaves a remainder of 5. (C5, C13, C21, etc.)

  3. #3
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    Thanks for suggestion. I have put in what I think is right but does not appear to be working!

    The actual data I need is is a worksheet called Daily Individual Sales, in cells L5, L13, L21 and then in intervals of 8 up to L245. Therefore in the cell I wanted the total of the cells to appear I typed:

    =SUMPRODUCT((MOD(ROW('Daily Individual Sales'!$L$5:'Daily Individual Sales'!$L$245),8)=5)*('Daily Individual Sales'!$L$5:'Daily Individual Sales'!$L$245))

    But all I get is the #VALUE! ( and yes there is data in cells L5, L13 etc!)

    Am I missing something obvious?!?!

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Rob, I setup a spreadsheet based on your specs. I have two sheets, "Sheet1" and "Daily Individual Sales".

    In column L of Daily Individual Sales, I filled in the number 1 from cells L1 to L250.

    In cell B18 of Sheet1 (random cell I chose), I put the formula you provided and it returned the proper sum (31, since there are 31 cells between 5 and 245 counting by 8, inclusive of 5 and 245).

    If you can't figure out why you're getting the value error, try zipping and uploading the sheet and we can take a look.

  5. #5
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    OK had to take a break from this pesky spreadhsheet but back now with the bullet between my teeth!!

    Are you saying that thie formula will tell me how many "cells" there are between L5 and L245 ?? IF so, I will file that away for future use. What I need is the sum of the values in every 8th cell between L5 and L245. So if my cells were as follows:
    L5 10
    L13 5
    L21 8
    L29 3

    then the magic formula i put in at cell x1 would give me the answer 26 as this is L5 + L13 + L21 + L29.

    I am not able to upload from this work pc ( dam national companies! ) but if it makes things easier I can email it to my home pc and then upload it.

    Thanks for your patience


    Rob

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Hi Rob,

    I just re-created the environment from our original discussion, and when I changed the values in L1:L250 from 1's to 2's, the formula returned double the amount. (31 changed to 62).

    I also changed the values in L5, L13, L21, etc.. and the formula returned the correct SUM each time.

    That would mean it is actually summing every 8th row in your range, not just counting every 8th row.

  7. #7
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    OK - losing my hair now!

    I created a new spreadsheet and as you say it all works fine on random data, just not with mine! Below is my data from the "Daily Individual Sales" worksheet which is now titled Sheet 1. I hoped that you could cut and paste this date into excel but dont think it will work??
    The columns are L and M, the "TOTAL DAYS" title starts at row 4. Where there is no data, its either because no days were sold or the date has not yet occured. Each row represents a different person. There are 6 people. I have tried to show this on the first set of data below:
    TOTAL DAYS TRANS
    0 0
    0 0
    0 0
    43 12
    56 14
    10 2

    TOTAL DAYS TRANS
    0


    40 11
    80 17
    64 15

    TOTAL DAYS TRANS
    50 8
    85 8
    27 4

    TOTAL DAYS TRANS
    25 7
    0
    0 7

    TOTAL DAYS TRANS
    0


    46 12

    76 16

    TOTAL DAYS TRANS
    0


    42 11
    8 4
    47 12

    TOTAL DAYS TRANS
    61 13
    40 9
    22 6


    TOTAL DAYS TRANS
    31 13
    61 15
    16 4

    TOTAL DAYS TRANS
    69 18
    75 14
    44 11


    TOTAL DAYS TRANS
    0

    28 7
    34 8
    53 9

    TOTAL DAYS TRANS
    0


    58 15
    66 15
    2 1

    TOTAL DAYS TRANS
    51 12
    29 7
    0 0

    TOTAL DAYS TRANS
    57 17
    31 8
    20 8

    TOTAL DAYS TRANS
    0

    28 11
    62 13
    63 18

    TOTAL DAYS TRANS
    0


    63 20

    88 19

    TOTAL DAYS TRANS
    0


    80 24

    88 21

    TOTAL DAYS TRANS
    41 8
    87 22
    10 3

    TOTAL DAYS TRANS
    56 9
    55 13
    34 9

    TOTAL DAYS TRANS
    0


    57 11
    58 17
    27 8

    TOTAL DAYS TRANS
    0


    24 8
    41 12
    19 5

    TOTAL DAYS TRANS
    69 12
    55 11
    2 1

    TOTAL DAYS TRANS
    52 10
    30 6
    9 2

    TOTAL DAYS TRANS
    0

    TOTAL DAYS TRANS
    0

    TOTAL DAYS TRANS
    0

    TOTAL DAYS TRANS
    0

    TOTAL DAYS TRANS

    TOTAL DAYS TRANS

    TOTAL DAYS TRANS

    TOTAL DAYS TRANS

    TOTAL DAYS TRANS
    Can you see anything weird expalining why the formula:

    =SUMPRODUCT((MOD(ROW(Sheet1!$L$5:$L$245),8)=5)*(Sheet1!$L5:$L245))

    would not work? The cell i am putting the formula in is Sheet 2, cell A1.
    Last edited by Simon Lloyd; 03-23-2007 at 10:42 PM.

  8. #8
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Rob, can you zip up and post a copy of your spreadsheet? It would be much easier to test on your file than for me to continue testing on ones I make myself that don't seem to have issues with the formula.

    Thanks.

  9. #9
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    Yes, will do tonight from home. Sorry to mess you around!


    Rob

  10. #10
    Registered User
    Join Date
    03-03-2007
    Posts
    14

    Talking

    OK! I am back home and have de-personalised the spreadsheet and removed any unneccesary sheets. If you take a look at the first sheet this is what we track each agents "sales" on. The next spreadsheet then totals up the figures from the 1st spreadsheet and the final chart spreadsheets give an "easy to view layout and chart for printing at the end of each month. There are assorted issues which I have made notes of in red highlight on each of the 3 sheet types!!! If anyone is really bored and wants to try fix them for me that would be great! Paul, if you can get the sumproduct to work then you are a genius!

    Thanks guys!

    Rob
    Attached Files Attached Files

  11. #11
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Rob, a few things:

    1. In your sheet, I suggest turning on automatic calculation (Tools->Options->Calculation->Automatic). That threw me off for a minute when any change I made to the formula wouldn't work.

    2. On the 'Daily Individual Sales' and 'Monthly Breakdown' sheets, anywhere you have used =SUM(X/Y), where X and Y are cell references (A1, M18, etc..), you don't need the SUM() wrapper. Simply =X/Y will work.

    ***BUT, if you want to hide all of the #DIV/0! errors (due to numbers being divided by 0 or blank cells) then use =IF(ISERROR(X/Y,"",X/Y)).

    3. Most importantly, I figured out why the SUMPRODUCT formula wasn't working in your sheet: you have text in that column which was generating a #VALUE! error. We can work around this, though, by using the double unary form of SUMPRODUCT instead, like so:

    Please Login or Register  to view this content.
    I also changed the "=5" part to "=MOD(ROW(L5),8)" so that if you drag the formula down or up, it will update the row reference and determine the remainder for comparison. Essentially, "MOD(ROW(L5),8" equals 5, "MOD(ROW(L8),8" equals 0, "MOD(ROW(L10),8" equals 2, etc.

    I will finish updating the formulas and hiding errors for you and get the sheet back to you by tomorrow morning.
    Last edited by Paul; 03-24-2007 at 02:48 AM.

  12. #12
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Here's the workbook back, with a few other notes...

    1. On your agent sheets, in the FPO% and Insurance % columns, the "TOTAL" row cells (C38 and I38) have nice long formulas to average the percentages not equal to zero, as shown below:

    =SUM(C6:C36)/SUMPRODUCT(--(LARGE((C6:C36),ROW(INDIRECT("1:"&COUNT(C6:C36))))<>0))
    =SUM(I6:I36)/SUMPRODUCT(--(LARGE((I6:I36),ROW(INDIRECT("1:"&COUNT(I6:I36))))<>0))

    But, this can be much simpler, like so:

    =SUM(C6:C36)/COUNTIF(C6:C36,">0")
    =SUM(I6:I36)/COUNTIF(I6:I36,">0")

    Or even slightly shorter, using an array formula (you have to press CTRL+SHIFT+ENTER after entering the formula, not just ENTER):

    =AVERAGE(IF(C6:C36>0,C6:C36))
    =AVERAGE(IF(I6:I36>0,I6:I36))

    I updated all of the Agent sheets cells C38 and I38 with the SUM/COUNTIF type formula above so you don't have to remember to use CTRL+SHIFT+ENTER if you ever go into those cells.

    ================================================

    2. On your Agent 1 sheet, you had a formula in column I:
    =SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5

    I added error correction to show 0% when a #DIV/0! error occurs, like so:
    =IF(ISERROR(SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5),0%,SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5)

    I did not update column I in sheets Agent 2 through Agent 6, though, since it had a different formula in column I on those sheets, which evaluated to a #REF! error. You were dividing by a "hidden" column of numbers (gray font/gray background) in col G? I'll let you decide what should be in those cells since I don't know an insurance % formula from a hole in the wall, to be honest.

    If you use the formula I added on Agent 1, you will have to create/adjust every formula from I6:I36 on each sheet (Agent 2 - Agent 6). This is 155 formula edits, but you'll get into a rhythm after a few.

    Simplest way would be to copy I6:I36 from Agent 1 and paste it into Agent 2 - 6 sheets. Then adjust the starting cell for each sheet. For example, on Agent 1 cell I6, the cell references in the formula are C5, E5 and L5. In I7, they're C13, E13 and L13. Now on Agent 2 cell I6, that same formula should reference C6, E6 and L6; followed by C14, E14 and L14 in I7. On Agent 3 cell I6, the formula should reference C7, E7 and L7 followed by C15, E15 and L15 in I7. I think you get the picture, and I couldn't come up with an easy way to automatically allow you to drag the formula down and have each of those increment by 8 (boy would that have made it easy!).

    ================================================

    3. I updated cell A1 on all of the sheets to reference 'Daily Individual Sales'!A1. Some of your #REF! errors are probably due to the fact that a sheet or cells were removed.

    ================================================

    4. On the 'Daily Individual Sales' sheet I added error correction on columns H and K to hide #DIV/0! errors.

    ================================================

    Well, I hope that solves most of the issues, even though there's a bit more work for you to enter in those Agent Insurance% formulas.. if you need further assistance be sure to let us know!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    Paul,

    Thank you so much for all the effort you have put in . I am about to go over your thorough notes but wanted to say thanks up front!!

    The grey blanked out cells you mentioned I cant even remeber why I did them. Will look at that as well!

    Ill post back after ive had a proper look but you are the top man!!

    Rob

  14. #14
    Registered User
    Join Date
    03-03-2007
    Posts
    14
    Yes, its offical. Paul is a STAR!

    Your changes work great and I managed to type in my 155 formulas ok. ( CHOCOLATE BAR TO WHOEVER COMES UP WITH A WAY TO AUTOFILL CORRECTLY IN THAT SITUATION)

    I got rid of the grey boxes. They were nothing to do with the insurance calculations, just a running total of the COE for use in the chart. I have now moved these figs down to bottom of sheet alongside the other chart info.

    The sheets look great now. I just have one more favour! My final questions for you is............. " On the Daliy Individual Sales sheet where no FPO sales are made, there is now a fancy blank which looks great. However this is creating a blank on the Agent sheet in the FPO column. That means that my source data for the chart does not convert it into NA# so my graph jumps to 0 and back which I was trying to avoid. If you look at the sheet and FPO graph it will prob make more sense. Is there a way of fixing this fix?!?!? That will be the last question I promise......well for tonight anyway!

    Thanks again Paul and anyone else that has helped me since I set out on this journey!

    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Rob_UK
    Yes, its offical. Paul is a STAR!

    Your changes work great and I managed to type in my 155 formulas ok. ( CHOCOLATE BAR TO WHOEVER COMES UP WITH A WAY TO AUTOFILL CORRECTLY IN THAT SITUATION)

    I got rid of the grey boxes. They were nothing to do with the insurance calculations, just a running total of the COE for use in the chart. I have now moved these figs down to bottom of sheet alongside the other chart info.

    The sheets look great now. I just have one more favour! My final questions for you is............. " On the Daliy Individual Sales sheet where no FPO sales are made, there is now a fancy blank which looks great. However this is creating a blank on the Agent sheet in the FPO column. That means that my source data for the chart does not convert it into NA# so my graph jumps to 0 and back which I was trying to avoid. If you look at the sheet and FPO graph it will prob make more sense. Is there a way of fixing this fix?!?!? That will be the last question I promise......well for tonight anyway!

    Thanks again Paul and anyone else that has helped me since I set out on this journey!

    Hi,

    to help yourself and Paul with the formula, in Agent1 C6, something like

    =INDIRECT("'Daily Individual Sales'!K"&(ROW()-5)*8-3)

    F6

    =INDIRECT("'Daily Individual Sales'!G"&((ROW()-5)*8-3))

    and in I6

    =IF(ISERROR(SUM(INDIRECT("'Daily Individual Sales'!C"&((ROW()-5)*8-3)&":E"&((ROW()-5)*8-3)))/INDIRECT("'Daily Individual Sales'!L"&((ROW()-5)*8-3))),0%,SUM(INDIRECT("'Daily Individual Sales'!C"&((ROW()-5)*8-3)&":E"&((ROW()-5)*8-3)))/INDIRECT("'Daily Individual Sales'!L"&((ROW()-5)*8-3)))

    or as
    Please Login or Register  to view this content.
    to obviate the web spaces,

    will formula fill down the columns, but I'll pass on the bar thanks.

    hth
    ---
    added, the -3 adjuster could be contained in a cell, say A1, being -3, -2, -1, 0, +1, +2 for the 6 sheets,
    then "&((ROW()-5)*8-3) would become "&((ROW()-5)*8+$A$1) the formula could then be used in all 6 sheets, however, this might be considered 'overkill' unless the number of agents is set to increase, and a simple modification to each of the 3 formulae be made for each sheet.
    ---
    Last edited by Bryan Hessey; 03-26-2007 at 09:26 PM.
    Si fractum non sit, noli id reficere.

  16. #16
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Bryan, thanks for the assistance with those formulas. Would have taken me a bit to come up with that last one.

    Rob, as for your 0's and NA()'s.. you should be able to change the formula in column K (FPO %) on the Daily Individual Sales sheet to:

    =IF(ISERROR(J5/M5),0%,J5/M5)

    It's currently set to =IF(ISERROR(J5/M5),"",J5/M5, which is making it blank.

    If you want to hide the 0.00%'s in just that column, you can create a Conditional Format on that column's cells so that 'Cell Value Is' -> 'Equal to' -> 0, and then set the font color to match the background color. When your formula evaluates to 0, you won't see it!

    If you want to hide all 0 values in your spreadsheets, use the Tools -> Options -> View -> Window Options section -> un-check Zero Values.

  17. #17
    Registered User
    Join Date
    03-03-2007
    Posts
    14

    Thumbs up

    Great stuff Bryan and Paul!

    Thanks again for your expert advice! Its amazing how powerful and complex Excel is until you really get your teeth into it. Without guys like you all us "basic users" would be in the kaka indefinently!!!!

    Cheers guys.

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08029 seconds
  • Memory Usage 9,351KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (17)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (17)postbit_legacy
  • (17)postbit_onlinestatus
  • (17)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1