+ Reply to Thread
Results 1 to 9 of 9

Varible minefield

  1. #1
    Registered User
    Join Date
    12-20-2014
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    10

    Varible minefield

    Hello all,

    I was hoping for some support or a solution to a problem I've ran into. I have a billing sheet exported from a database. I currently have the following formula that works correctly.

    =IF(J2="SD",IF(K2="IT",N2*38+38*0.25,(IF(K2="NT",N2*38,IF(K2="TO",0.25*22)))),IF(K2="IT",N2*43+43*0.25,(IF(K2="NT",N2*43,IF(K2="TO",0.25*22))))
    )*O2

    Think of the work as item, packing and delivery. This generates the correct cost for all work. My problem is that now we offer the following.

    If a given project (I:I) is ordered on the same day (F:F) of the same profile (L:L) then packing and delivery only applies once and all additional orders have only delivery costs (N*22) assigned.

    I have used the =COUNTIFS(I:I,I2,F:F,F2,L:L,L2) but this returns duplicate values of entries eg. 1 22 333 4444.

    I don't know how to merge these correctly.

    Thanks in advance. Your help is appreciated.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Varible minefield

    I have tried mocking this up, but don't follow what you mean by duplicate entries. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-20-2014
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    10

    Re: Varible minefield

    I have now attached a workbook which should help with explaining what I'm trying to achieve. I have included a "Desired Result" sheet that displays what was previously calculated manually. The "GridviewExport" is the sheet I'm playing with. Thanks for your help and please, dont hesitate in asking if anything is unclear.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-20-2014
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    10

    Re: Varible minefield

    Can anyone suggest a direction to solve this?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Varible minefield

    Trying to figure this out. So on "Desired Result" which columns are you looking for a formula for? Is this table self contained (except for where you're using a VLOOKUP) or does this information come from somewhere else?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    12-20-2014
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    10

    Re: Varible minefield

    The "Desired Results" sheet would be the old method of manual billing for your reference.

    Concentrate on the "GridViewExport" sheet. The "Total Cost" column Q is accurate for all individual orders. The problem is the discount offer.

    If a given project (I:I) is ordered on the same day (F:F) of the same profile (L:L) then packing and delivery only applies once and all additional orders have only delivery costs (N*22) assigned. Column "R" calculates this criteria and returns values that qualify.

    Column R value results
    1 (doesn't meet this discount criteria so cost in Q is correct)
    2 (cost in Q applies once plus discount for delivery only (N*22)
    3 (cost in Q applies once plus discount for 2 deliveries only (N*22) x 2

    I need to combine the formula for cost in column Q to accommodate this discount offered with reference to R.

  7. #7
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: Varible minefield

    Does the attached file work? I added a tab for pricing lookup. I also added a Unique ID column and a discount column (both in orange). I then updated the pricing formula (column Q). Basically I tried to make the pricing come together with index match, rather than an nested IF formula. Test the pricing with various options and see if gives you what you are looking for.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-20-2014
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    10

    Re: Varible minefield

    Hello jjhayes,

    I'm currently looking over your proposed solution but it will take a little time for me to get my head round. It looks good so far and thanks for introducing me to a whole new aspect of excel.

    Thanks a lot.

  9. #9
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: Varible minefield

    I hope it works out...I was a little confused by the pricing options...but it appears there are different options based on 1) Video Standard, 2) Trailer Option, and 3) Discount (if Barcode, StartedOn and Profile Match) all match. I was little confused on Trailer Transcode cost for HDTO vs SDTO (5.5 vs 9.5 ?) but I did my best to translate your if formula correctly.

    If the Trailer transcode cost for HDTO should be 10.75 (43*.25, just change cell D7 on Pricing Lookup tab from 5.5 to 10.75) and the formula will update accordingly.
    Last edited by jjhayes; 07-13-2015 at 06:38 PM.

+ 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. Using Cell Value as a varible
    By Cupragsw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2014, 09:17 AM
  2. name worksheet by usıng a varible
    By stevekirk in forum Excel General
    Replies: 1
    Last Post: 08-24-2006, 11:52 PM
  3. varible table not
    By spxer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2006, 12:13 PM
  4. varible table
    By spxer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2006, 03:35 PM
  5. [SOLVED] Offsetting a varible??
    By Ashley Milford via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2005, 11:05 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