# Varible minefield

1. ## 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.

2. ## 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

3. ## 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.

4. ## Re: Varible minefield

Can anyone suggest a direction to solve this?

5. ## 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?

6. ## 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. ## 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.

8. ## 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. ## 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.

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

#### 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