# hard: create a discount function in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### number

hi,

I would like to create a series of discount functions in excel based on a certain product. For example, I am selling 10 brands and each of these brands has a different discount.
Say that I prepare an economical offer in excel, I'd like a function that as soon as I digit the name of that certain brand, the cost of that item, it gives me at once the rate of discount I have for that specific brand. Does this makes sense?
Say i am selling a sony camcorder, I know that with sony I have 50% of discount, I would like that when I write any number (the cost of the item) under the name sony (which excel should recognize) then it would apply at once a discount.
I know it's quite complicated but I hope someone wll help me thanks!!

#### XL Guru

Using a discount lookup table & the VLOOKUP function would be one option. See the attached pic.

HTH,
Andy

#### Attachments

• 48.9 KB Views: 113

#### number

thank you XL Guru, I got only an intuition of how it could work..
what are the necessary steps to create the VLOOKUP function?
my result is that if I write a certain name, then, the relative cell of the price with discount, by writing the price it should apply at once the discount I have assigned for that specific brand..

#### XL Guru

To create the formula in cell F2 from scratch:

Select F2, then click the Paste Function button (fx) (or press Shift+F3). This kicks off the Paste Function dialog.

Make sure Function Category = All. Scroll down in the Function Name listbox until you see VLOOKUP, & click on it. Click OK.

In the subsequent dialog (which you can drag around to see the worksheet better):

Click in Lookup_value, then click on cell E2.
Click in Table_Array, then click & drag to select A2:C4. Press Shift+Home to highlight all of the reference, then press F4 to add in the "\$"s.
Type 2 in Col_Index_Num (i.e. price is in the 2nd column of the lookup table).
Type FALSE in Range_lookup.

Click OK.

Anne

#### XL Guru

Dreamboat said:
You know me, ever the slowcoach. /

#### jiml8

##### Guest
I would suggest that you reconsider your approach.

Why do you want to do this in excel? You really need a database to do this. You'll find that the whole thing will be simpler, more logical, more easily maintained, and much more easily extended if you use a database.

Access would work, though I don't like Access, and you probably already have it.

#### number

Sorry guys, I don't really succeed to understand how to apply the VLOOKUP in what I want to do:

I have a price list with item name, code, price and discount.

I need to do an economical offer IN A SEPARATE SHEET and I would like that when I type a certain code article, it would give me its price and its discount, based on the other separate excel sheet.
As far as I am seeing, VLOOKUP cannot connect different sheets can it?

Do I have to use the VBS functions?

I don't want to use access, cause I prepare the economical offers in excel, so rather than going to look for the price and discount of a certain article in other excel files, I want to create queries and assignations and connect databases in excel. I know it is possible to do it, I just need to find out how.
I know it's a quite difficult operation, anyway,
Thank you very much

#### number

let's see, yes I can upload my file cause the price list I want to refer to it is an .xls file.
Sorry if I'm being dump, but can you illustrate me step by step how can I:
1) I need to create an economical offer so I have a sheet with : item code, item brand, price, discount.
2) I would like that when I go in the first cell "item code" and I write a specific coude, in the cell "price" it will give me the relative discount.

Thanks mate!

#### number

I know sorry but I don't understand anything of what you're saying.
As I was mentioning, I would like to insert an "if" a certain name on a certain column is sony "THEN" the discount is..... and in this cell I'd like to upload the desired discount from the sony discount list for that specific item.
The process I'm describing is a bit more articulated, I don't get what you're saying.

#### XL Guru

number said:
I would like to insert an "if" a certain name on a certain column is sony "THEN" the discount is..... and in this cell I'd like to upload the desired discount from the sony discount list for that specific item.
The VLOOKUP function will do this for you. If you'd like to upload your file, I'll plug in some formulas.

#### number

please do, can you illustrate step by step how to do it? I really need it.
it's weird because in the VLOOKUP function I didn't fine the import...
but then even if I could import, can you tell me EXACTLY how to ASSIGN a certain number when I write a certain name?
thanks a lot

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On