multiple formulas needed in one cell to create a value in a new cell

Try AVERAGIFS.

Hi bob,

Thanks for your reply. Yes, I am using AVERAGIFS in the spreadsheet already, though for somewhat of a simpler formula. Do you think AVERAGIFS would be able to handle the type of formula I am trying to achieve here though, as it's somewhat longer and more complex?

Thanks.

Hi there,

Sorry but would anyone know if this is possible?

Thanks.

Hi there,

I've been advised I can't have a formula and value in the same cell. Hence I've added a column, which makes the formula somewhat simpler (I hope!). I've also added another explanation of what I'm trying to achieve in this sheet, which explains things more clearly (again, I hope!).

Thanks!

The formula that gets you the .4 in E10 is:

Please Login or Register  to view this content.

I have no clue where you get the 2.5 rating from using this information, so that part is up to you.

Here's the new attachment. As referred to in your private message.

DBY

Thanks DBY.

This is very very close to what I am looking for! The formulas are set up perfectly in the “Tables – Avg Daily Sales” tab, i.e. working out the average depending on what medium and TV/radio supplier is selected.

The “Sale” score comparison formulas are set-up perfectly in column E of the first tab as well. The only thing is, is it possible to do the same for campaign? That is, compare campaign against campaign for each region. It uses the same scale as the “Sale” event and the scores in column E add to the same average as the sale scores, so hopefully it’s not too difficult to set-up. Having said that I tried and broke the sheet haha.

I’ve uploaded an updated sheet based on your workings, which basically just turns column B into a data validation list.

Thanks for this and your ongoing help. I promise I shan't harass you again after I sort this final thing out.

Hello
It's OK, no harassment. I assume you just want to repeat the set up for Campaigns. I've just added a new column 'Campaign Comparison' and a new sheet 'Tables - AVG Daily Campaign' and repeated the formulas for that criteria. The Region/Store drop down list was broken so I've corrected that and remember, if you need to add more than 3 items for the Radio and TV drop downs, you'll have to expand the named ranges 'TV' and 'Radio'.

Hope it all works for you but any problems come back and myself or another forum member might be able to help.

DBY

Thanks DBY. No idea why those legends are all messed up like that but thanks for fixing!

To all, would it be possible to combine the campaign and sale comparison in column E? So in other words, depending on what is selected in column B (Sale or Campaign), the formula in column E would know to compare to either all the sales for that region, or all the campaigns for that region. No problems if not, just wondering!

Lastly, and this should be fairly easy (I think), I am happy for the Campaign comparison scores to contribute to the same average in the "Tables - Avg Daily Sales" sheet. Hence, although I'm very grateful for it, there wouldn't be a need for that final sheet that was created.

Thanks.

Hello Jamie
I thought a few tweaks might be needed. I didn't realize you wished to combine the 'Sale' and 'Campaign' figures. I've amended the previous attachment. See Version 3 (attached). Is this nearer the mark?

DBY

Hi
Glad you're getting near to what you want. As I mentioned, if you added more items to the Data Validation lists you would have to expand the 'TV' and 'Radio' named ranges to take in the new entries. I've done that, and they seem fine to me. Unfortunately, I don't think you can lock columns in the table and tab to a new row without removing the protection. As I suggested you could hide the 'Comparison' column and the table would function as normal.

DBY

Perfect. Thanks a lot for your help!