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

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

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

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

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

my first 'IF' formula
 

from help index search for INT
INT
See Also

Rounds a number down to the nearest integer.

Syntax

INT(number)

Number is the real number you want to round down to an integer.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.


Selecting an example from Help

3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.


1
2
A
Data
19.5
Formula Description (Result)
=INT(8.9) Rounds 8.9 down (8)
=INT(-8.9) Rounds -8.9 down (-9)
=A2-INT(A2) Returns the decimal part of a positive real number
in cell A2 (0.5)



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

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

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