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%
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.
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?
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 ..
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.