If function to return no. based on 2 cells?

**duffer** · 10-14-2008, 09:55 AM

Hi everyone,

I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).

What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.

Is there such a formula or do I have to enter manually the amounts into an If function?

Thanks guys an gals!

**EdMac** · 10-14-2008, 10:09 AM

Suggest you use a Lookup or Vlookup function

see the attached example of VLOOKUP or this for lookup

http://www.techonthenet.com/excel/formulas/lookup.php

**duffer** · 10-15-2008, 02:56 AM

Originally Posted by EdMac

Suggest you use a Lookup or Vlookup function

see the attached example of VLOOKUP or this for lookup

http://www.techonthenet.com/excel/formulas/lookup.php

Hi Edmac,

It gets more complicated... This is formula (=SE is =IF in Italian) I am using:

=SE(E4=0;"0,00";SE(E4=1;"128,10";SE(E4=2;"155,10";SE(E4=3;"222,60";SE(E4=4;"263,00";SE(E4=5;"333,90";SE(E4=6;"371,00";SE(E4=7;"438,40";SE(E4=8;"492,40";SE(E4=9;"546,30";SE(E4=10;"607,00")))))))))))

My problem is that these are the prices for 1 post code and I have about 10 post codes in total, therefore I need to have 10 different formulae.

Would Lookup help me in this case??

**ratcat** · 10-15-2008, 03:07 AM

G'day duffer,

Could you supply an example of your data layout.

But I'm have problems picturing the layout you are using.

I have an idea of using a dynamic lookup.

Thanks

**duffer** · 10-15-2008, 03:59 AM

Ciao,

Thanks. Please find attached an example of what I'm trying to do. I've done the formula for the first and last row because they have the same price.

Now imagine if I am doing this for every week and there are 10 or more different prices.

I don't have experience of Vlookup but if it would work then that would be purrrfect!

I am using an Italian package therefore we use ; instead of , to seperate the functions.

Thanks!

**ratcat** · 10-15-2008, 05:12 AM

G'day duffer,

Too your layout the Vlookup is no good. Different layout sometimes needs different formulas

But the Index and a nesting Match formula works beautifully.

Cell B4

Please Login or Register  to view this content.

This formula has a IF statement wrap to return a blank cell

Please Login or Register  to view this content.

See attached. xl should change the , to ; for you in the formulas

Attached is a xl 97-2003 version.

**xld** · 10-15-2008, 05:39 AM

Try this

=IF(OR(C4<0,C4>10),0,CHOOSE(C4+1,0,128.1,155.1,222.6,263,333.9,371,438.4,492.4,546.3,607))

**duffer** · 10-15-2008, 05:39 AM

Ciao ratcat,
I think this could be purrrfect!
As Colombo would say 'just one more thing': In the example there are 5 weeks, but if I needed to show for 52 weeks, how can I copy the formula across without the range being shifted, i.e. the formula chooses the correct cell for each week but chooses the incorrect range - it should always be $N4:$X4,MATCH(C4,$N$2:$X$2 ??

Thanks for this. It's been bugging me as I thought I would have to manually enter each formula and I've been trying to put it off for days now.

I hope you have a g'day!

**ratcat** · 10-15-2008, 06:08 AM

Well duffer.....

The "$" symbol tells xl to make the cell stay put at that reference.
Practice on a blank, sheet very basic formulas on different combinations when copying the formula at different place on the spreadsheet.
Click and drag on the node on the edit box (located on the bottom right hand of box).

Click here for further info

With your spreadsheet

What I did was type the cell out in C4 then copy down
then with that cell range highlighted I copy (Ctrl C) the range
then move the edit box to the next cell (D4) and paste (Ctrl V)
then move to the next cell (F4) and etc etc and etc.

So if you get cell reference right, you only type once.

Cheers

**duffer** · 10-15-2008, 06:16 AM

Fantastic you are my god!

**duffer** · 10-15-2008, 08:52 AM

Originally Posted by xld

Try this

=IF(OR(C4<0,C4>10),0,CHOOSE(C4+1,0,128.1,155.1,222.6,263,333.9,371,438.4,492.4,546.3,607))

thanks xld, it sure is better than my attempt. i do prefer the function ratcat provided as it's easier to manage should there be additional prices, price changes, more postal areas to add at a later date. ciao for now, duffer.

**xld** · 10-15-2008, 09:10 AM

No problem, just keep it in your back pocket for the next time :-)

If function to return no. based on 2 cells?

LinkBack

Thread Tools

Rate This Thread

Display

If function to return no. based on 2 cells?

Thread Information

Users Browsing this Thread

Similar Threads

Return a reference from LARGE function

If statement meant to return blank cells doesn't return blank cells?

Grouping cells based on their contents

writing a formula to return non-empty cells

Return value from a function

Bookmarks

Bookmarks

Posting Permissions