Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
I've never done anything beyond the most basic formula. Can someone tell me
where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
Instead of IF, try this where b21 contains your cost.
=(B21*1.14)/(0.75+INT(B21)*0.03) -- Don Guillett SalesAid Software "Boze" wrote in message ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
You can indeed do that with an IF formula, but you will be limited to 7
embedded IFs. You'd rather use a VLOOKUP formula, like: Assuming cost is in A1 =A1*1.14*VLOOKUP(A1,{0,0.75;100,0.78;200,0.81},2,1 ) Fill in your array between { and } HTH -- AP "Boze" a écrit dans le message de ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
Wow.. that works perfectly! Thank you! I don't understand exactly HOW it
works. In playing around with it I see that for every dollar increase in cost the divisor is increased by 0.03. Maybe the key is for me to understand INT. Would it be quite complex if the divisor didn't always change by the same increment for every dollar? Ie, Cost 1 to 99¢ = *1.14 / 0.75 1.00 to 1.99 = *1.14 /0.78 2.00 to 2.99 = *1.14/0.80 3.00 to 3.99 = *1.14/0.82 or if the divisor changes by the same increment but the price grouping isn't always a whole dollar? Cost 2.00 to 2.49 = *1.14/0.80 2.50 to 2.99 = *1.14/0.81 3.00 to 3.49 = *1.14/0.82 3.50 to 3.99 = *1.14/0.83 We haven't got the formulas set in stone so will probably go with whatever's easy to setup and still get us a reasonably fair price Thank you! "Don Guillett" wrote in message ... Instead of IF, try this where b21 contains your cost. =(B21*1.14)/(0.75+INT(B21)*0.03) -- Don Guillett SalesAid Software "Boze" wrote in message ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
Create a table in say M1:N10 with these values
0 0.75 1 0.78 2 0.8 2.5 0.81 3 0.82 3.5 0.83 and then use a formula of =A1*1.14/VLOOKUP(A1,I1:J4,2,TRUE) where A1 might hold the value to evaluate. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Boze" wrote in message ... Wow.. that works perfectly! Thank you! I don't understand exactly HOW it works. In playing around with it I see that for every dollar increase in cost the divisor is increased by 0.03. Maybe the key is for me to understand INT. Would it be quite complex if the divisor didn't always change by the same increment for every dollar? Ie, Cost 1 to 99¢ = *1.14 / 0.75 1.00 to 1.99 = *1.14 /0.78 2.00 to 2.99 = *1.14/0.80 3.00 to 3.99 = *1.14/0.82 or if the divisor changes by the same increment but the price grouping isn't always a whole dollar? Cost 2.00 to 2.49 = *1.14/0.80 2.50 to 2.99 = *1.14/0.81 3.00 to 3.49 = *1.14/0.82 3.50 to 3.99 = *1.14/0.83 We haven't got the formulas set in stone so will probably go with whatever's easy to setup and still get us a reasonably fair price Thank you! "Don Guillett" wrote in message ... Instead of IF, try this where b21 contains your cost. =(B21*1.14)/(0.75+INT(B21)*0.03) -- Don Guillett SalesAid Software "Boze" wrote in message ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
or, in addition to what Bob said you could modify this.
=LOOKUP($B$12,{0,5,10,100,250;0,0.045,0.85,0.09,8. 75}) -- Don Guillett SalesAid Software "Boze" wrote in message ... Wow.. that works perfectly! Thank you! I don't understand exactly HOW it works. In playing around with it I see that for every dollar increase in cost the divisor is increased by 0.03. Maybe the key is for me to understand INT. Would it be quite complex if the divisor didn't always change by the same increment for every dollar? Ie, Cost 1 to 99¢ = *1.14 / 0.75 1.00 to 1.99 = *1.14 /0.78 2.00 to 2.99 = *1.14/0.80 3.00 to 3.99 = *1.14/0.82 or if the divisor changes by the same increment but the price grouping isn't always a whole dollar? Cost 2.00 to 2.49 = *1.14/0.80 2.50 to 2.99 = *1.14/0.81 3.00 to 3.49 = *1.14/0.82 3.50 to 3.99 = *1.14/0.83 We haven't got the formulas set in stone so will probably go with whatever's easy to setup and still get us a reasonably fair price Thank you! "Don Guillett" wrote in message ... Instead of IF, try this where b21 contains your cost. =(B21*1.14)/(0.75+INT(B21)*0.03) -- Don Guillett SalesAid Software "Boze" wrote in message ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first 'IF' formula
Thank you all. Looks like I've got a couple approaches I can use.
Appreciate the great help Boze "Boze" wrote in message ... I've never done anything beyond the most basic formula. Can someone tell me where to begin for a formula for our pricing? We have our cost and want to use different formulas based on the cost If the cost is 99¢ or less we take the cost * 1.14 then divide by .75 If the cost $1 to $1.99 we take the cost * 1.14 then divide by .78 This would continue for $2 to $2.99, $3 to $3.99, etc Thanks in advance Boze |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |