Using Multiple Formulas in A Single Cell

[jlsCreative]

Hello all,

I'm building a cost calculator and I need to use different formulas in a single cell. I have an end price that could meet three pricing conditions, here they are:

Small Paperback Book:
Page Count: 18-46, $1.04 per unit
Page Count: 48-106, $1.61 per unit
Page Count: 108+, $0.63 per unit

I need to program one cell to return calculations based on how many number of pages another cell has. What is the correct syntax here? I'm a newbie to Excel and am building this for a client as a bonus and am not keen on syntax.

I tried the following:
=if(B2<46,1.04) OR if(B2>46<106,1.61) OR if(B2>108,0.63)

But, that's returning an error. Any help would be appreciated. As of now, I cannot post attachments, because my account is new, but if you have a Gmail account, I can share the document with you via Google Docs.

Thanks,
Jason

 

[kevatarvind]

use below one

=IF(AND(B1<=47,B1>17),"$1.04",IF(AND(B1<=107,B1>47),"$1.61",IF(B1>=108,"$0.63","0")))

 

[jlsCreative]

Thanks!! Worked like a charm!

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G
1		20	1.04	$20.80		0	0
2			1.04	$20.80		18	1.04
3			1.04	$20.80		48	1.61
4			1.04	$20.80		108	0.63
5		or use the following
6		20	$20.80
7		200	$126.00
3a

Excel 2003

Cell Formulas
Range	Formula
C1	=LOOKUP(B1,$F$1:$G$4)
C2	=IF(AND(B1<=47,B1>17),1.04,IF(AND(B1<=107,B1>47),1.61,IF(B1>=108,0.63,"0")))
C3	=(B1<=47)*(B1>17)*1.04+(B1<=107)*(B1>47)*1.61+(B1>=108)*0.63
C4	=LOOKUP(B1,{0,0;18,1.04;48,1.61;108,0.63})
C6	=B6*LOOKUP(B6,$F$1:$G$4)
C7	=B7*LOOKUP(B7,$F$1:$G$4)
D1	=$B$1*C1

The above shows a variety of solutions.
I would probably use the Lookup with a table (range showing the rate structure).

 

[West Man]

Slightly shorter: =IF(B1<48),"$1.04",IF(B1<108),"$1.61","$.63"))

Assuming that B1 can never be less than 18. Also keep in mind that in your original scenario, books with pages of 47 and 107 are not handled.

Additionally if this cell will be used for calculation then you will wish to substitute 1.04 in place of "$1.04" etc.