+ Reply to Thread
Results 1 to 28 of 28

Calculating range and 'remainder'

  1. #1
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Calculating range and 'remainder'

    It's been a few years since I did any serious work with formulas and I'd appreciate a bit of help with the attached worksheet please.

    Hopefully it's clear from the examples given in the section on the right. On entering Number in C13 I want to see a result that shows the folder and position in the form 'Folder,Position'.

    It's clearly a matter of first calculating Folder by testing Number against the respective folder sizes. Then, probably using the function MOD(number,divisor), calculating Position. And finally separating the two by a comma.

    I've been floundering for an hour or two but not got beyond an initial IF. Am I expecting too much from formulas and might instead need a macro?
    Attached Files Attached Files
    Terry, East Grinstead, UK
    Excel 365, Win 10 Pro, i7 PC, 4.0 GHz

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Calculating range and 'remainder'

    Hello,
    Can you clarify how "position" is calculated?

  3. #3
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    As shown in the examples. For instance, if Number = 5, then it does not fit in the first folder (which holds only 4 entries) so Folder = 2 with a remainder of 1, so Position = 1. Of course, that's only an initial stage. I've since made further progress in calculating Folder, and my formula now looks like this:
    =IF($C$13<=C2,$A$2,(IF($C$13<=C3,$A$3,(IF($C$13<=C4,1,"Outside whole range")))))

    It's still wrong somewhere though! But hopefully you see where I'm heading. In its present form, that will only get me Folder. Presumably I'll need another formula in another column to get Position?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Calculating range and 'remainder'

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Thanks, that looks very close! It's fine in the first two ranges, and in the early part of folder 3. But fails somewhere in range 3. I'm trying to pin it down but I'm sure you'll spot it before me.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Calculating range and 'remainder'

    What number does it fail on?

  7. #7
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Not quite as close as I thought!

    Please Login or Register  to view this content.
    Last edited by terrypin; 05-07-2022 at 10:52 AM.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Calculating range and 'remainder'

    Please try

    =LET(n,G2,z,$B$2:$B$4,m,MMULT(--(ROW(z)>TRANSPOSE(ROW(z))),z)+1,f,MATCH(n,m),IF(n>SUM(z),"1,1",f&","&n+1-LOOKUP(n,m)))

    or with LAMBDA

    =LET(n,G2,z,$B$2:$B$4,m,SCAN(0,z,LAMBDA(a,v,a+v))-z+1,f,MATCH(n,m),IF(n>SUM(z),"1,1",f&","&n+1-LOOKUP(n,m)))
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating range and 'remainder'

    A non-365 formula using column D (sse attached)

    =IF($H2>MAX($D$2:$D$11),"1,1",INDEX($A$2:$A$11,MATCH($H2,$D$2:$D$11,1))&","&$H2-INDEX($D$2:$D$11,MATCH($H2,$D$2:$D$11,1))+1)
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  10. #10
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    If 18 is 2,14 and 345 is 3,327, shouldn't 346 be 3,328?

  11. #11
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    @josephteh:

    Thanks, spotted that and thought I'd corrected it an hour or so ago, but have done so now.

  12. #12
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    Another possible formula:=IF(G2>MAX($C$2:$C$11),1,XMATCH(G2,$C$2:$C$11,1))&","&IF(OR(G2=1,G2>MAX($C$2:$C$11)),1,G2-INDEX($C$2:$C$11,IF(G2>MAX($C$2:$C$11),1,XMATCH(G2,$C$2:$C$11,1))-1))

  13. #13
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Bo_Ry: Doesn't work here, but then I don't see how it could? You have no references to the input Number at $C$13. And you refer to G2, which is part of the examples I included.

  14. #14
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    Amend formula to referring to C13=IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))&","&IF(OR(C13<$C$2,C13>MAX($C$2:$C$11)),1,C13-INDEX($C$2:$C$11,IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))-1))

  15. #15
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    See my reply to Bo-Ry.

    Also see my worksheet, cell A13. Input Number is in C13.

    Also:
    "Hopefully it's clear from the examples given in the section on the right."

  16. #16
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    Amending above formula:=IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))&","&IF(OR(C13<=$C$2,C13>MAX($C$2:$C$11)),1,C13-INDEX($C$2:$C$11,IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))-1))

  17. #17
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    What is your expected result if C13=2 or 3 or 4?

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Calculating range and 'remainder'

    Quote Originally Posted by terrypin View Post
    See my reply to Bo-Ry.

    Also see my worksheet, cell A13. Input Number is in C13.

    Also:
    "Hopefully it's clear from the examples given in the section on the right."

    Hopefully, someone might know how to change G2 to C13

    =LET(n,G2,z,$B$2:$B$4,m,MMULT(--(ROW(z)>TRANSPOSE(ROW(z))),z)+1,f,MATCH(n,m),IF(n>SUM(z),"1,1",f&","&n+1-LOOKUP(n,m)))

    =LET(n,C13,z,$B$2:$B$4,m,MMULT(--(ROW(z)>TRANSPOSE(ROW(z))),z)+1,f,MATCH(n,m),IF(n>SUM(z),"1,1",f&","&n+1-LOOKUP(n,m)))
    Attached Files Attached Files

  19. #19
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Quote Originally Posted by josephteh View Post
    Amending above formula:=IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))&","&IF(OR(C13<=$C$2,C13>MAX($C$2:$C$11)),1,C13-INDEX($C$2:$C$11,IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))-1))
    Nearly! All results for any number <= 4 in C13 gave 1,1 here. The rest looks good

  20. #20
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Excellent, thanks Bo_Ry, perfect!

  21. #21
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Quote Originally Posted by josephteh View Post
    What is your expected result if C13=2 or 3 or 4?
    Like the example in H2.
    IOW, 2==> 1,2 and 3 ==> 1,3

  22. #22
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Bingo, works for all values, thank you!

    --------------------

    I'd never have come up with either your or Bo_Ry's solutions. Will have to study them both in detail later, meanwhile very happy to have working solutions for my current project.

    Still like to know if my 'indented IFs' approach stood a chance! Will revisit later. Its advantage is that it was sort of intuitive. IOW close to the logical approach I'd take manually. With Folder known I reckon it would have then been relatively easy to get Position.
    Last edited by terrypin; 05-07-2022 at 11:53 AM.

  23. #23
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Quote Originally Posted by JohnTopley View Post
    A non-365 formula using column D (sse attached)

    =IF($H2>MAX($D$2:$D$11),"1,1",INDEX($A$2:$A$11,MATCH($H2,$D$2:$D$11,1))&","&$H2-INDEX($D$2:$D$11,MATCH($H2,$D$2:$D$11,1))+1)
    Last one I tested and works perfectly, thanks John! I hadn't thought of using my example column as a convenient source too. Sorry to all if it caused ambiguity.

  24. #24
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    Quote Originally Posted by terrypin View Post
    Like the example in H2.
    IOW, 2==> 1,2 and 3 ==> 1,3
    Amended formula:=IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))&","&IF(C13<=$C$2,C13,IF(C13>MAX($C$2:$C$11),1,C13-INDEX($C$2:$C$11,IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))-1)))

  25. #25
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Quote Originally Posted by josephteh View Post
    Amended formula:=IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))&","&IF(C13<=$C$2,C13,IF(C13>MAX($C$2:$C$11),1,C13-INDEX($C$2:$C$11,IF(C13>MAX($C$2:$C$11),1,XMATCH(C13,$C$2:$C$11,1))-1)))
    Great, my favourite solution, thanks! With a little more study sometime of XMATCH and INDEX I reckon I'll actually understand it. I thrive in copy/paste mode but so much prefer to know what I'm doing.
    Last edited by terrypin; 05-08-2022 at 10:21 AM.

  26. #26
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    I tried to get two of these 'conversion' formulas into a single worksheet, as attached. The only column referenced in the original formula (I'm using Joseph's latest) is C. So I thought I could simply change that to H throughout. Clearly not! What is my mistake please?
    Attached Files Attached Files

  27. #27
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Calculating range and 'remainder'

    Your range has expanded from C2:C11 to C2:C21, so formula for D2 should change to:
    Please Login or Register  to view this content.
    Copy D2 to I2.

  28. #28
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Calculating range and 'remainder'

    Of course, embarrassing, thanks!

+ 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. [SOLVED] Order / sort range by number in bracket first then remainder alphabetically.
    By skyping in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2013, 08:14 AM
  2. Problem with remainder
    By Markj99 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2013, 04:12 PM
  3. [SOLVED] VBA to grey out the remainder of row
    By Memsi123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 08:35 AM
  4. Replies: 3
    Last Post: 09-29-2008, 07:28 AM
  5. Return remainder value
    By skchow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2007, 04:27 AM
  6. Replies: 2
    Last Post: 11-15-2006, 11:04 PM
  7. Remainder
    By Kevin H. Stecyk in forum Excel General
    Replies: 3
    Last Post: 05-17-2005, 05:06 PM
  8. [SOLVED] remainder in a division
    By sinbad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2005, 05:06 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