If you are great with excel, I need help badly

[Kifle]

Ok, so I'm trying to create a commission payout sheet to make my life simple with my horrid sales people; so, what I'm needing is some way to assign a numerical value to text and use it in a formula. So, as an example:


Product Sale Price Profit Net Profit


This is how the cells are currently set up. I will need to input the product name and have it represent a value to place in the formula located in the "Sale Price" column which will subtract a numerical representation in the "product" column from the numerical value entered into the "sale price" column. I have a complete item list and cost price exported onto another sheet in the same workbook, so I'm not sure if there's some cell naming trick I could pull out my ass. Anyway, if anyone has an answer to this question, either post here or call me at 1-800-trannysex. Gracias.

[Corth]

Can I just prank call you instead for shits and giggles?

[Pril]

Signing Kifle up for random calls! :)

[Ragorn]

I don't get it... can you give an example? Populate a row for me.

[Kifle]

Yeah, Rag:

Ex:

Product Sale Price Cost Gross Profit Net Profit

Soda Can $20.00 $12.00 $8.00 $7.66


So, on a separate sheet I have an entry "soda can" and two columns over I have $12, as an example. I have the entire product line and price on this sheet. On the sheet with the example, I would like to type in the product name and have the cost price added in automatically. I would then enter in the sale price and it would calculate everything.

Bottom line, I need to attach a numerical value into Soda Can (much like a constant does in any formula) and either remove the cost column or have it output the numerical value to the cost column.

[Sarvis]

You can create a "list" of the names, and the associated values and then use vlookup to find the value based on the product name.

So say you defined the following columns:

Code: Select all

A              B
Shoes   10
Dresses   12
Purses   20
Boots    25
Socks           28


Then vlookup would look like this:


=VLOOKUP("Boots",$A:$B,2,FALSE)

And would return 25. (That first parameter can refer to another cell of course.)

[Kifle]

Corth/Pril, remember, I have caller ID and will not hesitate to return the favor. Also, it's a company paid cell phone, so I can just change my number at company cost :)

[Ragorn]

Sarvis is on the right track.

In cell A1, type the name of the product as it appears on your data sheet.

In cell B1, type in the sale price.

In cell C1, put the following formula:
=IF(ISERROR(VLOOKUP(A1,'Name of data sheet'!$A:$E,3,0))," ",VLOOKUP(A1,'Name of data sheet'!$A:$E,3,0))
This will go to your data sheet and look everything from cell A1 to E25 as a table. It will search column A on your data sheet for the item name you put in cell A1. When it finds it, it will return the value in the Column C, which is your cost.

In cell D1, put the following formula:
=IF(ISERROR($B1-$C1)," ",$B1-$C1)
This will subtract the cost from the sale price, and give you your gross profit. If you haven't typed anything into B1 yet, then D1 will be blank.

I don't know what to type in column E1, because I don't know how you calculate your net profit.

Then, copy the formulas in C1 and D1 and paste them in as many rows as you need. What will happen is, the chart will start blank. When you type a name into A1, C1 will automatically populate with the item's cost. If you have a name in A1, and you type a sale price into B1, then D1 will automatically populate with the profit.

Is that what you're looking for?

[kiryan]

The lookup function is what you need

vlookup(VALUE [ the text you are typing in], TABLE ARRAY [upper left and bottom right coordinates of your product price sales data], COLUMN INDEX [the column that contains the data you want as a result], FALSE)

In my example below, table array = A:C (columns A through C), could just as easily be A1:C3 or whatever makes sense for your data. Column index for sale discount would be 3, for sales price it would be 2. The "False" at the end... Most excel functions default to "approximate match" which means if you are looking for E and you have D then F, it will give you the value for F... vlookup defaults to exact match, but I'd put it in just to be explicit, its something you always need to pay attention to in Excel. Additionally, although false makes it return "N/A" if it doesn't find an exact match, its not case sensitive, c will match C. leading and trailing space will be matched as they should be. (I often have to do a search and replace replacing 2 spaces with a single space and repeat until I get 0 matches).

Lastly, the column you are matching (product name), must be the first column in your "table array" and it must be sorted in ascending order. If you want to match on column B, define your table array as B1:C. If column A has the result data and B the match data , you will need to reorder your columns.

Code: Select all

Sheet 1
        column A Product Name    -   colum b Price                                   -   column C Sale Price

row1, WidgetA                        -     =vlookup(A1,Sheet2!A:C, 2, false)  -   =(B1-vlookup(A1,Sheet2!A:C, 3, false))

Sheet 2
Column A name, Column B price, column C discount
WidgetA, 1.99, 1.00
WidgetB, 20.00, 5.00
WidgetC, 25.00, 10.00


[Ragorn]

VLOOKUP doesn't require data to be sorted in ascending order.

[kiryan]

ragorn is right. the old functions, lookup and match required it I believe. Should you the $ like Sarvis posted.

If you cut and paste a formula (like into the next row), excel will shift it over columns or down rows automatically. So A1-C3 would become B1-D1 if you copied the formula down one row. Colums A:C would become B:D if you shifted over a column. To counteract that $ sets an permanent reference so $A$1-$C$3 would permanently fix the beginning on A1 and end on C3 or $A:$C for fixed columns. Note you could do $A1, C$3 which would only fix the starting column A and the ending row 3.

[Sarvis]

VLOOKUP doesn't require data to be sorted in ascending order.

It does according to the help... at least in Excel 2007...

[Pril]

Your internet sucks more than Lilithelle!

[kiryan]

I tested it.. and it worked out of order in 2007 as Ragorn indicated. I always sort and do a lot of verification when working with lookup and match. It doesn't always seem to do what I want done.

[Ragorn]

VLOOKUP doesn't require data to be sorted in ascending order.

It does according to the help... at least in Excel 2007...

LOOKUP does. VLOOKUP and HLOOKUP do not.

[Kifle]

It be fixed. Thanks for the help guys :) Thanks for calling, Mike! We used vlookup. I was just using lookup before and had things kinda fucked.... and yes, the internet around here gets shitty for some reason from time to time. Blame Verizon for that one.

[Ragorn]

I called and got your voicemail, btw ;)

[Pril]

I called and got your voicemail, btw ;)

Probably because his internet sucks :)

[Kifle]

It's usually good. I think Pril was using my network as a proxy to download his gay porn at work.

[Pril]

I was pulling the gay porn straight from your hard drive.

[Kifle]

I unfortunately share a computer with the owner... well, the owner sometimes comes in and kicks me off of my computer to look up shit like sex tourism in costa rica; no shit, I actually came in to work that day and the only window open was firefox which had a costa rican prostitution site with a back log of sex tourism searches in google. I was amazed and scared.

[Corth]

Let me guess.. used car dealership?!

[Kifle]

Lol, no, but ethically close... I run a ditribution warehouse which sells bongs. I feel as though it's the nest application of my studies.

[Corth]

Rofl - so much better! So how do your sales guys earn commissions selling bongs? I mean.. do you have a network of stoners on college campuses selling them one at a time and pocketing a few bucks on each one? Or are we talking regional sales guys selling to all the head shops in a hundred mile radius?!

[Kifle]

Rofl - so much better! So how do your sales guys earn commissions selling bongs? I mean.. do you have a network of stoners on college campuses selling them one at a time and pocketing a few bucks on each one? Or are we talking regional sales guys selling to all the head shops in a hundred mile radius?!

The last one, but nationwide. I hit the big time, ma!