calculating cost

[coccoster]

Hey guys,

Need to create a spreadsheet that calculates the Advertising Cost of a vehicle which is defined by Category.

Ill include or the information needed to work this out.

[teylyn]

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.

[coccoster]

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.

Sorry what i need is how i would layout the spreadsheet in order to calculate this.I should have been more clear.

[teylyn]

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

[coccoster]

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

Calculate This- With the data for used cars sold i have been given i need to work out what is the most affective way to advertise. There are Website, Magazine and Newspaper ads (both local and state). What i need to calculate is which type of advertising campaign. How often it is to be advertised, the duration is weeks, and any additional one of costs,

[teylyn]

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.

[coccoster]

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.

Ahk..

Well ill need to lookup data therefore, VLOOKUP
Also need to use IF statement to calculate duration (already worked that out)

[teylyn]

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.

[coccoster]

I need to lookup data in the Advertising cost spreadsheet
The results go into a output sheet on the same worksheet.

[teylyn]

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.

[coccoster]

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.

[teylyn]

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.

[coccoster]

What exactly do you need there?

Sorry ignore that second sheet, i sent the wrong one ill up the right one now, sorry mate.

Where does that sale price come from?

Comes from data given to us, its just how much the car is for sale.. Linked from another Spreadsheet which i dont think i attached. Ill attach all spreadsheets now sorry mate.

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

We have been told that cars over the price of 25k are ran twice a week cars under 25k are ran only once a week.\

[teylyn]

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.

[coccoster]

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.

Think there are a few other coloums like "Advertising Costs" (forgot to put that one in)

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?

I presume type to mean 3lines or 5lines in regards to State or Local Newspapers(defined in Sheet 1) I have no examples to give you other then the one provided a few posts back, sorry.

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?

Additional costs are one of costs that occur if the advertisment is a web add, e.g The web advertising costs in sheet 1.

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.

Again the only expected results i have been giving is the one i posted a few posts ago,


Thanks for helping out, really do appreciate it

[teylyn]

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

[coccoster]

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

Yer no worries, thanks alot for that, Enjoy sleep time lol

[coccoster]

Just need it done by Wednesday Aussie time. Cheers

[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.

[coccoster]

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

[coccoster]

That clearer now?

[coccoster]

Think i got it, just need help on how to add up two values using VLOOKUP.
See attached xls, look at sheet2

[teylyn]

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)