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

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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!!
 
Joined
Aug 30, 2003
Messages
2,702
Using a discount lookup table & the VLOOKUP function would be one option. See the attached pic.

HTH,
Andy
 

Attachments

number

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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..
 
Joined
Aug 30, 2003
Messages
2,702
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.
 

jiml8

Guest
Joined
Jul 2, 2005
Messages
2,634
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

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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!
 
Joined
Aug 30, 2003
Messages
2,702
To upload your file, use the "Manage Attachments" button under "Additional Options".
 

number

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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.
 
Joined
Aug 30, 2003
Messages
2,702
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

Thread Starter
Joined
Oct 15, 2003
Messages
1,057
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top