If = between range than * by this.

[Ashman88]

Hi,

I am trying to setup a spreadsheet for costings on weights.

Users will enter in weight in cell 1 on sheet 1 and cost will appear in cell 2 on sheet 1. But different weight ranges are equal to different value. I have create a separate sheet (2) for the weight prices as these need to be changed form time to time.

Example below

When users enters in value to cell 1 it needs to look up range in other sheet, check what the number is bewteen than multiply cell 1 by corresponding price and show result in cell 2.


Sheet 2 Example

	a	b	c
1	Start	Finish	Price
2	1	300	.5
3	301	500	.4
4	501	800	.2
5	800		.1

<tbody>
</tbody>

Where 800 is in A5 this will be anything above 800


Thanks,
Ashman

 

[vds1]

Try,

=LOOKUP(D2,Sheet1!A2:A5,Sheet1!C2:C5)

where D2 is the cell where user enters the value and sheet1 is the lookup sheet.

Sheet1!A2:A5 is the start
Sheet1!C2:C5 is the price

Note that A2 to A5 in lookup sheet should be in order.

 

[RCBricker]

This will work with the example you gave (change sheet names to what you need). Also you had two instances for weight 800, following your logic I made the last entry in column A 801 rather than a second instance of 800.

=IF(AND(A2>=Sheet142!$A$2,A2<=Sheet142!$B$2),Sheet142!$C$2,IF(AND(A2>=Sheet142!$A$3,A2<=Sheet142!$B$3),Sheet142!$C$3,IF(AND(A2>=Sheet142!$A$4,A2<=Sheet142!$B$4),Sheet142!$C$4,Sheet142!$C$5)))

This assumes a separate column for the total weight * Price

If (as you stated) you wanted the calculated value in cell B2 on down use this:

=IF(AND(A11>=Sheet142!$A$2,A11<=Sheet142!$B$2),Sheet142!$C$2*A11,IF(AND(A11>=Sheet142!$A$3,A11<=Sheet142!$B$3),Sheet142!$C$3*A11,IF(AND(A11>=Sheet142!$A$4,A11<=Sheet142!$B$4),Sheet142!$C$4*A11,Sheet142!$C$5*A11)))

 

[Ashman88]

Hi vds1,

This worked perfectly. I did have to modify to this =LOOKUP(G6,'Sheet1'!$D$4:$D$8,(G6*'sheet1'!$F$4:$F$8)

Is there anyway to put in a isblank or to have the cell2 show as 0 if cell 1 has no data. At the moment it shows #N/A. Later i will need to create rows of the formula above with a SUM at the bottom of cell 2

 

[vds1]

Perhaps you can try IFERROR,

=IFERROR(LOOKUP(G6,'Sheet1'!$D$4:$D$8,(G6*'sheet1'!$F$4:$F$8),"")

 

[Ashman88]

Thanks VDS1. That works perfectly.