# 'If' function help

1. ## 'If' function help

Hi all,

I am attempting to enter a formula into excel, such that if the annual volume falls in a certain range, then the fee per pack updates accordingly (i.e., the higher the volume, the lower the fee per pack). I have attempted to use the 'IF' function,and have posted below the formula that I have entered, but this does not seem to be working. Could anybody advise please? Thank you in advance!

=IF(B4<=899999, [B19], IF(AND(B4>=900000, B4<=999999), [B20], IF(AND(B4>=1000000, B4<=1099999), [B21], IF(AND(B4>=1100000, B4<=1199999), [B22], IF(AND(B4>=1200000, B4<=1299999), [B23], IF(AND(B4>=1300000, B4<=1399999), [B24], IF(AND(B4>=1400000, B4<=1499999), [B25], [B26])))))))

Excel screenshot.png

2. ## Re: 'If' function help

Hi,

Welcome to the Forum.

Modify your IF formula as below:

``Please Login or Register  to view this content.``

3. ## Re: 'If' function help

Hi,

This may be better addressed with a combination of index and match.

Use match to find the next largest number in the range \$A\$19:\$A\$26, then return the corresponding value in \$B\$19:\$B\$26.

To do this, change B19 to zero as you don't have a specific case between 800000 and 900000,

Then use this formula in C4

Formula:
`Please Login or Register  to view this content.`

4. ## Re: 'If' function help

a couple things, one, is [B19] reference to a cell? if so it shouldn't have the brackets around it.
two... =IF(B4<=899999, [B19], IF(AND(B4>=900000, B4<=999999), [B20], IF(AND(B4>=1000000, B4<=1099999), [B21], IF(AND(B4>=1100000, B4<=1199999), [B22], IF(AND(B4>=1200000, B4<=1299999), [B23], IF(AND(B4>=1300000, B4<=1399999), [B24], IF(AND(B4>=1400000, B4<=1499999), [B25], [B26])))))))
can be shortened to this... =IF(B4<=899999, B19, IF(B4<=999999,B20, IF(B4<=1099999,B21, IF(B4<=1199999,B22,IF(B4<=1299999,B23,IF(b4<=1399999,B24,IF(B4<=1499999,B25, B26)))))
An if statement will stop working once the value is found so you don't need the ANDs in it. I didn't count the right parens ) so I don't know if I got the right number after removing the additional ANDs. Hope that helps.
Also if the [B19] etc refer to a cell, you might want to consider a vlookup with a true statement to refer to a table of the values instead.

5. ## Re: 'If' function help

welcome to the forum. try:
=LOOKUP(B4,\$A\$19:\$A\$26,\$B\$19:\$B\$26)

1 thing though. do upload an excel sample so that we do not have to manually key in your data to do a testing. input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand. 1,240,000 for eg. what should the answer in C4 be?

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

6. ## Re: 'If' function help

Originally Posted by cbatrody
Hi,

Welcome to the Forum.

Modify your IF formula as below:

``Please Login or Register  to view this content.``
This worked! Thank you so much for your quick response - I'm a bit of an excel novice! You have made my day.

7. ## Re: 'If' function help

Or try:

=VLOOKUP(B4,\$A\$19:\$B\$26,2,1)

8. ## Re: 'If' function help

This worked! Thank you so much for your quick response - I'm a bit of an excel novice! You have made my day.
You are welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1