calculating cost

Sorry, but we won't do your homework for you. If you have a specific question about how to use a function or how to do a calculation, please ask, but don't expect us to do all your work.

Can you put into your own words what "calculate this" would be?

I meant: can you put in words what the calculation needs to be? Before you can start thinking about formulas, you should have an idea of what you want to do. Do you need to look up data or compare data? Sum, average, whatever? If you sepecify the logic, someone here can give you the formula and/structure translated into Excel. But you need to be able to describe the steps that lead to the result. If not in Excel terms, then in plain words.

What do you need to lookup? Where is the data? What is the criterion? Where do the results need to go?

Please don't quote whole posts. It's just clutter.

What data do you need to look up?

What is the criterion? What is the expected result? Give me a few examples of input and expected results. Do it manually, and I can give you a formula to do it automatically.

Ok.

for a car allocated to a “commercial” car category, Sale price of $27,990, the total advertising cost should come out to be $1125.

Sale price is over 25k therefore duration is 2 a week for state 1 a week for Local
Commercial is a 5 line Advertisement
5 Line prices are: 50 and 60 for local (two types of local papers) and 80 and 65 for State (again 2 types of state papers) there's a 100 web advertising fee and a 225 magazine advertising fee for commercial category cars

The data is in the Advertsing spreadsheet i provided in OP.

Sorry, I don't understand. In the Workbook you attached in you post above, there are two sheets.

The "Processing sheet" has two tables. The "Advertising Cost" sheet has one table, but only the row headers have data. I assume you want to fill the "local" and "state" columns in that sheet with some kind of data.

What exactly do you need there?

for a car allocated to a “commercial” car category, Sale price of $27,990,

Where does that sale price come from?

Sale price is over 25k therefore duration is 2 a week for state 1 a week for Local

Even if the car sale price were recognisable in your data sample, where is the logic for the "therefore"??

duration is 2 a week for state 1 a week for Local

Can you say that in plain terms? I have no clue what that means.

There's something missing here.

It's getting clearer now.

By the way, "mate" is actually "china plate".

So, in your newly uploaded "Advertising Cost.xls" you have the advertising cost on one sheet. On Sheet2 you have a list of cars with reggo, category and sales price.
Two column are empty.

I assume the point of this exercise is to fill these two columns with data that has some connection to the other file you uploaded.

Question 1: What is "Type" and what do you want to see in that column for each vehicle? Can you give an example of the expected result?

Question 2: What is "Additional Cost" and what do you want to see in that column for each vehicle? Can you give an example of the expected result?

Are there any other calculations that you want to perform? If so, give examples of the expected result and explain the logic that would lead to the result in plain words.

Can you give me some time to mull this over? It's nearly bed time on the East Island and I've had a full day. I'll look into it tomorrow, OK?

I've got a lot more to go on now, with your latest upload and specs, and I'm sure we'll work out a solution.

cheers

so. Looking at this now. I'm afraid I can't make much of the Word file in your first attachment.

Please explain what you want to do. Work out manually what the result for the first three vehicles on Sheet 2 should be and explain how you arrived at these results.

Originally Posted by teylyn

so. Looking at this now. I'm afraid I can't make much of the Word file in your first attachment.

Please explain what you want to do. Work out manually what the result for the first three vehicles on Sheet 2 should be and explain how you arrived at these results.

Ok, pretty much what it does is:

I have a heading called Type, under that is two sub headings one State (for state newspaper advertising) and one Local (for local newspaper advertising). Need it to lookup the category for each car e.g Sedan (has 3lines in local and 3 lines in state) Need a formula that will display either 3 line, 5 line or pictorial for the state and local coloums.

Additional Costs column looks up if there are any costs in the Website Advertising costs or Magazine advert columns in sheet 1.




Duration is how many times in a week it is showed, the car is put in for a 2 week add, so if the add is showed twice a week, in total it is showed 4 times.

Advertising will then do:

"commercial” car category, to be sold for $27,990, the total advertising cost should come out
to be $1125."

Explaining that:

Commercial = Web Advertising Cost ($100) + Magazine Advert($225) + (leader($50)+times($60)) *2 + (age($80) + herald($65)) * 4

further explaing:

(leader($50)+times($60)) *2

Commercial is a 5line advertisment therefore the 50 + 60, * 2 because 110(50+60) is the price for a 1 week add. we need 2 weeks)

(age($80) + herald($65)) * 4

Commercial is a 5line advertisment therefore the 80 + 65, *4 is because the 80+65 is the price for one add, this add is run twice a week, wee need it to run for 2 weeks, therefore 4 adds in total

That formula there will give me $1125

I have updated the Advertising Sheet, take a look. NEW

no time to look at post #20, in a rush and out the door in a minute. To add 2 vlookups, just use

=vlookup(whatever)+vlookup(theotherone)