Hi guys i'm havin a few pirblems with this question. i'm trying to write a formulae that would check for 2 conditions then return a value. Below is the problem i'm trying to solve. I have tried to use a combination of IF and vlookup statements but my formulae is not running as it should ....any help with this would be greatly appreciated.

If the customer is a library, school or educational institutions:

No of books purchased Discount %
More than 50 Books 30%
25 to 49 books 20%
15 to 24 books 15%
5 to 14 books 10%
Less than 5 books 5%


If customer is an individual:

On purchase of no. of books Discount %
More than 25 books 25%
5 to 24 books 15%
Less than 5 books 0%

correction ..multiple conditions..in the form of customer type and number of books purchased..based on this ..the formulae should output the discount.

Hi,

This is a "double lookup" which can be achieved using an INDEX() MATCH() formula.

The key point in this case is to make sure you have your "Books Purchased" sorted smallest to largest in your lookup table so you can pass 1 into the MATCH() match_type parameter.

I've attached a simple example to demonstrate this.

Hope that helps...

Hey thanks alot. I've used a couple of if statements and it worked fine ..now i have to do over the spreadsheet with VB coding to achieve the same results as the If statement formuale... Can you assit me with this please.

I have attached a copy of the spreadsheet.

I am very new to this coding..Many Thanks if you can assist me.

You can use VBA to put these formulas in the cells. If you don't want the formulas to remain then you can convert the result to a hard value. You can get an idea of the syntax by using the macro recorder to record yourself putting the formulas in the cells.

Hope that helps...

Hey thanks

I am really trying to understand ..i'm new to this and is trying really hard to learn ..what i wanna do is convert this formulae to VB coding ...

=(IF(A6<>"individual",IF(B6>=50,30,IF(AND(B6>=25,B 6<=49),20,IF(AND(B6>=15,B6<=24),15,IF(AND(B6>=5,B6 <=14),10,IF(B6<5,5,0))))),IF(B6>=25,25,IF(AND(B6>= 5,B6<=24),15,IF(B6<5,0,"-")))))/100

Hi,

You really shouldn't be using long IF() formulas like that - it's hard to read and, more importantly, it's hard to maintain. For example, what if one of the discount rates changes? You would have to go through and amend all the formulas. Your original idea of using a look up table is much easier to understand and easy to maintain. My original reply shows you how to do a "double lookup" in a formula.

Using formulas in this situation is also better than using VBA because the solution is simpler and therefore easier for you to maintain and for your users to understand. Why do you want to use VBA?

Hope that helps...

Ok no problem thanks. I understand ..now the VBA aspect of it i have to do it as well ..it's kinda for a project..so i have to show 2 ways of achieving the same results i.e via formulae and VBA

Many Thanks

Right... so this is your homework...

The simple answer is that with the richness and power of Excel formulas available, you wouldn't use Select or If statements for this: you would either evaluate a formula expression within VBA (see Evaluate method in your VBA helpfile) or you would use the Range.Formula property to insert the formula into the cell and then convert the result of the formula to a hard value using the Range.Value property.

ok kool thanks ...can u give me an example of how i should begin this ...i am really confused..i read up alot on this but still not grasping the concept..like for e.g. how to list the formuale within the syntax...i am relly trying hard at this...i guess it's like this because i never did programming before...can u help please..and thanks for the e.g on the double vlookup i customized it and it works perfectly ..

Many Thanks

Hi,

A really good starting point to determine the syntax is to use the macro recorder.

Turn on the recorder.
Type the formula into the cell.
[If you want the value hard coded then copy + pastespecial values]
Turn off the recorder.
Go into the Visual Basic Editor and have a look at the code it recorded.

What code do you get?

This is the result i received but this don't seems to work properly.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C10:R7C13,MATCH(RC[-1],R3C9:R7C9,1),MATCH(RC[-2],R2C10:R2C13,0))"
Range("C4").Select
End Sub