# Choose from a column dependent upon a value

1. ## Choose from a column dependent upon a value

Please see attached. I want the formula in G2 to say: IF the value in G11 is less than 250, then display "Below min order value", IF between 250-£499, return adjacent value in column B, IF between £500-£999, return column C, IF between £1000-£1999, return column D, IF above £2000, return column E.
Book3.xlsx

2. ## Re: Choose from a column dependent upon a value

You can put a nested If statement in H2: =IF(\$G\$11<250,"Below min order value",IF(\$G\$11<500,B2,IF(\$G\$11<1000,C2,IF(\$G\$11<2000,D2,E2))))

3. ## Re: Choose from a column dependent upon a value

The problem is that because C11 is summing column C, it is creating a circular reference. Any ideas on how to solve this?
Book4.xlsx

4. ## Re: Choose from a column dependent upon a value

Sorry, I mean G11 and column G.

5. ## Re: Choose from a column dependent upon a value

That's why I suggested to put the formula in column H, not G. There is a way to allow circular references, but you need the iterations defined. File>Options>Formulas>Enable iterative calculation. However, that won't solve this problem as the total for G11 will always be 0 because G11 just sums everything above it, which the line item totals in your first workbook post would be over-written by the formula I wrote if put in G.

So I think the best bet would be to put the formula in H, or if you want to keep it in G, you need to change the total order value formula to:
Formula:
`Please Login or Register  to view this content.`

and put this formula in G2:
Formula:
`Please Login or Register  to view this content.`

6. ## Re: Choose from a column dependent upon a value

Sorry Jeff, I haven't managed to do this. I will put it in the Commercial Services section and you can complete the form there for me if you wish. Many thanks.

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