• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

nested if formula

L

lightfastfocus

New Member
Hi Friends,


I love the challenge of excel and i've had some help from "hui", but i'm still having problems.


What i'm trying to do is automate my work process resulting a better work flow. The challenge is a long list of variables.


Essentially quantities x Cost.


Some tables would look like....


Units #


Print Charges

min $63.25

45-99 $1.40

100-249 $1.26

250-499 $1.20

500-1000 $1.15

1000+ $1.07


Print foil and Emboss

min $63.25

45-99 $64.40

100-249 $57.96

250-499 $55.20

500-1000 $52.90

1000+ $49.22


there are other variables and my worksheet looks ok, i thought using this method would reduce the number of cells required. Any help would be appreciated!


Dave
 
http://chandoo.org/forums/topic/posting-a-sample-workbook


Have a read through this. It has several options.
 
Hey Dan,


This really is a simple sheet, but right now the best i can do. I only enter into the green cells. the bottom section that is green i have to do by calculator and add in and the "existing block" normally is a minus amount.


thanks


Dave


https://docs.google.com/spreadsheet/ccc?key=0AnqrILbhI28ddFJsaWJzOGNzY1ZoNTJEOWw3ZERJRGc
 
I'm still not totally clear on what this thing is doing.


But try this:


http://dl.dropbox.com/u/1275899/Product%20quote%20sheet.xlsx


Maybe I'm totally misreading this though.


All you have to do is add quantities in column B. With the exception of C9, you can specify the price. C9 looks up the price on that table. Play with it a bit.
 
Hi Dave ,


If I understand you correctly , you have several charges in the process of production , which are variable , depending on the quantity ordered. Thus , in order to get a final quote , you need to multiply the order quantity by these variable charges.


In order to do this , one design can be to have the variable charge headers in a particular row , across several columns ; thus the row 5 , say starting from B5 , will contain the headers "Print Charges" , "Print Foil and Emboss" , "Wiro Replacement" and so on.


In column A , starting from cell A6 , have the quantity slots as row headers i.e. "minimum" , "45 - 99" , "100 - 249" and so on.


Then in the cells B6 , C6 ,.... , B7 , C7 ,... have the variable charges.


When a customer orders a quantity , say 37 , lookup this quantity in the charges table and retrieve the relevant charge , which you can multiply by the quantity to get the total cost. This charges table can be on a separate worksheet , and possibly protected so that the data entry can be done by anyone , while the rate changes can be done only by authorised persons.


In order to make the formula easier to define , the word "minimum" can be replaced by "1 - 44" so that the lookup can be standardised.


Narayan
 
Hi Dan_l and Narayan,


Thank you for your feedback, Narayan, your spot on with what i'm trying to achieve! My customers will call me 5 times a day with a variation to their first order and i spend my life glued to a calculator as some quotes can be very complicated and errors cost me my income / Bonus.


So i'm not after an "if" formula, more like a lookup table. Thanks Dan i'll have a look at your link. I really appreciate both your help, i'm amazed people are so willing. AWESOME.
 
Thanks Dan_l, i checked out the link. i think i get what you mean and i can see how the flow works, similar to Narayan's idea. So essentially i'm setting up a Table that i refer to based on the "conditions" and units required?


Narayan, can you help with a visual or some links to get me started.


Dave
 
Hi Dave ,


I think the following links give you information from the experts themselves :


1. http://www.cpearson.com/excel/TablesAndLookups.aspx


2. http://j-walk.com/ss/excel/usertips/tip020.htm


3. http://www.contextures.com/xlfunctions02.html


4. http://www.techonthenet.com/excel/formulas/vlookup.php


5. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


Of course , any time you need the smallest help , just drop in here !


Narayan
 
Hi Dave ,


The following links give you information straight from the experts themselves :


1. http://www.cpearson.com/excel/TablesAndLookups.aspx


2. http://j-walk.com/ss/excel/usertips/tip020.htm


3. http://www.contextures.com/xlfunctions02.html


4. http://www.techonthenet.com/excel/formulas/vlookup.php


5. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


Of course , any time you need the smallest help , just drop in here !


Narayan
 
Hi ,


Sorry for the delay.


You cannot do better than get it straight from the experts :


1. http://www.cpearson.com/excel/TablesAndLookups.aspx


2. http://j-walk.com/ss/excel/usertips/tip020.htm


3. http://www.contextures.com/xlfunctions02.html


4. http://www.techonthenet.com/excel/formulas/vlookup.php


5. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


Of course , if you want the slightest help , you can drop in here !


Narayan
 
Hi ,


Sorry for the delay.


You cannot do better than get it straight from the experts :


1. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


2. http://www.techonthenet.com/excel/formulas/vlookup.php


3. http://www.contextures.com/xlfunctions02.html


4. http://www.cpearson.com/excel/TablesAndLookups.aspx


5. http://j-walk.com/ss/excel/usertips/tip020.htm


Of course , if you want the slightest help , you can drop in here !


Narayan
 
Hi ,


Sorry for the delay.


You cannot do better than get it straight from the experts :


1. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


2. http://www.techonthenet.com/excel/formulas/vlookup.php


3. http://www.contextures.com/xlfunctions02.html


4. http://www.cpearson.com/excel/TablesAndLookups.aspx


5. http://j-walk.com/ss/excel/usertips/tip020.htm


Of course , if you want the slightest help , you can drop in here !


Narayan
 
Hi ,


Sorry for the delay.


You cannot do better than get it straight from the experts :


1. http://www.exceltip.com/excel_tips/Lookup_Formulas/112.html


2. http://www.techonthenet.com/excel/formulas/vlookup.php


3. http://www.contextures.com/xlfunctions02.html


4. http://www.cpearson.com/excel/TablesAndLookups.aspx


5. http://j-walk.com/ss/excel/usertips/tip020.htm


Of course , if you want the slightest help , you can drop in here !


Narayan
 
Hi ,


I just came across this great site :


http://blog.excelmasterseries.com/


The article dated Jan 9 , 2012 is on :


Looking Up a Quantity Discount In a Distant Excel Table With VLOOKUP


Narayan
 
Hi Dave ,


Sorry for the delay.


Can you check the following worksheet ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21159


Please do not click on the hyperlink ; copy + paste the entire address in your browser.


The formulae may not be implementing the correct calculations ; if they are not doing what you want to be done , change them according to your requirements ; or you can post your requirements here so that I can correct them and re-upload the worksheet.


Narayan
 
Hi narayan, looks sweet. i couldn't download it at all. It kept DL as a powerpoint file then i couldn't open it or check the formulas.


Could you briefly explain what you have done?


thanks


Dave
 
You must log in or register to reply here.
Back
Top