Creating a formula to calculate a result that depends on 3/4 criterias been met

1. Creating a formula to calculate a result that depends on 3/4 criterias been met

Firstly its probably best you download the file from below and open it up.

http://www.mediafire.com/?un3smhmdyzt

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.

If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

Best of luck!

2. You have multiple matches for those 3 items combined...

I assume you want the first match then?

If so, try, in S7:

``Please Login or Register  to view this content.``

This formula must be confirmed with CTRL+SHIFT+ENTER
not just ENTER.. you will see { } brackets appear around the formula... Then copy down.

If you type in MAIN STORES in S27 and add another match on the end of all the others matches that should sort it. So that the order to match is account number, container waste stream and whatever is in S27 to match with column B in sheet 'container price list' that will then give the right price from the correct row.

I'll have to make sure that Column S in sheet March is amended then for all rows.

4. Sorry not doing very well today. You'd have to insert another column on sheet 'March' in between Container and Lift £ and then do the above^

5. You mean you would insert a new column S in March sheet to indicate the Main Store? If so, what is the matching column in the Container Price List sheet?

6. Okay, so the formula is not much shorter, but at least you now just enter it normally (i.e with just Enter) and then copy down:

``Please Login or Register  to view this content.``

7. *EDIT*

That sorted it!

Thanks again.

8. Yet Another Twist

http://www.mediafire.com/?mtwbxttmxwt

I need to edit the formula on sheet ‘March’ in column T that shows the Lift £.

At the moment it shows the relevant price depending on certain conditions been met.

For example on row 1448 it shows in column T the price of £1,351.46. To determine this the formula has looked at the data on sheet ‘Container Price List’ and matched up the appropriate cell by looking at the Account number, Waste Type, Bin Type and Location (called Site Name on sheet ‘Container Price List’). The price displayed is the exchange price on sheet ‘Container Price List’ however; if that equals 0 then the price for Rental is shown instead (this been the case for cell T1448).

Now the problem is it doesn’t take into account the number of lifts (exchanges). For example in cell T1474 the price for 1 exchange is shown at £80.39. The problem is there were 2 exchanges (shown in cell Q1474). Therefore the price in T1474 should be multiplied by cell Q1474.

Now there is a twist because if the price is a rental price as in T1448 it shouldn’t be multiplied by Q1448. Instead it should look in sheet ‘Container Price List’ and the relevant cell in column J which in this instance is J206 and perform the calculation J206*4.33/the rental price in H206

9. Assuming you want the formula to start in T6:

``Please Login or Register  to view this content.``

10. ***edit***

11. instead of performing the calculation J206*4.33/the rental price in H206

could you change it to take the rental price in H206 and divide it by the answer of J206*4.33

basically swap it round?

**EDIT**

SORRY... it should yield the answer 72.50 to 2d.p.

12. You mean?

``Please Login or Register  to view this content.``

13. Yep that works! Thank you

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