If = between range than * by this.

Ashman88

New Member
Joined
Oct 31, 2014
Messages
3
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

abc
1StartFinishPrice
21300.5
3301500.4
4501800.2
5800 .1

<tbody>
</tbody>

Where 800 is in A5 this will be anything above 800


Thanks,
Ashman
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,189
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

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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)))
 
Last edited:

Ashman88

New Member
Joined
Oct 31, 2014
Messages
3
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,189
Perhaps you can try IFERROR,

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

Watch MrExcel Video

Forum statistics

Threads
1,099,368
Messages
5,468,213
Members
406,572
Latest member
Sidboi21

This Week's Hot Topics

Top