+ Reply to Thread
Results 1 to 5 of 5

Mod() Function Replacement

  1. #1
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Mod() Function Replacement

    Excel formulas really aren't my forte and I suspect the answer will be straightforward - foggy head bit too much beer on a school night!

    I have a list of revenues that I am trying to break down into the number of discrete items.

    Each item is generally one of 3 prices - 13.00,14.10,15.00 (there may be others, but these are the majority so I'm not concerned with other amounts)

    I'm using the formula
    PHP Code: 
    =IF(MOD(A2,14.1)=0,A2/14.1,IF(MOD(A2,13)=0,A2/13,A2/15)) 
    Which gives the following results:
    Account Revenue Total Items
    14.1 1
    14.1 1
    26 2
    14.1 1
    14.1 1
    28.2 2
    13 1
    14.1 1
    14.1 1
    14.1 1
    42.3 2.82
    84.6 5.64

    The last two lines are not the expected results - should be 3 and 6 respectively. I understand that this is to do with the way that the numbers are stored by excel, but I'm not sure how to adapt the formula to one that will work.

    I've attached a workbook of the sample if anyone wants it.

    Thanks for your help
    Attached Files Attached Files
    Last edited by Kyle123; 03-08-2012 at 06:08 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,702

    Re: Mod() Function Replacement

    Try this version

    =A2/IF(A2/14.1=INT(A2/14.1),14.1,IF(A2/13=INT(A2/13),13,15))
    Audere est facere

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Mod() Function Replacement

    Brill, thanks DLL

    BTW would you happen to be Barry Houdini on SO?

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,702

    Re: Mod() Function Replacement

    Yes, I'm "barry houdini" at several other places.......that avatar is a giveaway isn't it?!

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Mod() Function Replacement

    haha that and the fancy formulas...

+ 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