**Question description:**

*This user has given permission to use the problem statement for this blog.*

Include at least three functions, properly labeled also Include an additional function or formula that has at least one reference locked so that if it is dragged-and-dropped the reference does not change

Solved by A. Y. in 31 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
02/04/2018 - 02:32

Hello! Welcome to Excel chat.

User
02/04/2018 - 02:32

hi I am working in excel in onedrive

Excelchat Expert
02/04/2018 - 02:32

Ok.

User
02/04/2018 - 02:32

I have been working with your guys already

User
02/04/2018 - 02:33

I will post the link so you can access the file

Excelchat Expert
02/04/2018 - 02:33

Ok.

User
02/04/2018 - 02:33

https://1drv.ms/x/s!AjIl0Yzl0RMEgRT24dmXVsb2f7Ev

User
02/04/2018 - 02:33

I only have an hour and a half to finish this work

User
02/04/2018 - 02:33

just fyi

Excelchat Expert
02/04/2018 - 02:34

I understand. I can take only 1 question in this session.

Excelchat Expert
02/04/2018 - 02:34

I understand that you need a formula where the reference doesn't change.

User
02/04/2018 - 02:34

ok well thats fine

User
02/04/2018 - 02:35

I need the formulas f

User
02/04/2018 - 02:35

then I can use another session

User
02/04/2018 - 02:35

for the last question

Excelchat Expert
02/04/2018 - 02:35

Sure.

User
02/04/2018 - 02:35

are you linked to the file?

Excelchat Expert
02/04/2018 - 02:35

Yes. I am.

User
02/04/2018 - 02:35

I am running low on time

Excelchat Expert
02/04/2018 - 02:36

Please let me know which formula you want to work on.

Excelchat Expert
02/04/2018 - 02:36

You can lock the reference in a formula by using $ sign in front of column and row labels.

User
02/04/2018 - 02:37

You can try to define cpc as a index for search volumn
For keyword, you can try to use it as a keyword search criteria
And the cpc value will be based on keyword text

Excelchat Expert
02/04/2018 - 02:37

For example, you can lock the reference to cell A1 by using $A$1 in your formula. Using this will ensure that when you drag the formula down or across the cells, it won't be changed.

Excelchat Expert
02/04/2018 - 02:39

I didn't get that CPC. What's the formula you want to use there?

User
02/04/2018 - 02:39

ok thanks for the info

User
02/04/2018 - 02:39

I am new to formulas I really dont know

User
02/04/2018 - 02:39

or understand

User
02/04/2018 - 02:39

how about the sum function

Excelchat Expert
02/04/2018 - 02:40

I can use SUM function, but what you want to add?

User
02/04/2018 - 02:40

search volume

User
02/04/2018 - 02:40

does that make sense?

Excelchat Expert
02/04/2018 - 02:41

Ok.

User
02/04/2018 - 02:41

also as you can see I created a formulas page not sure if that helps

Excelchat Expert
02/04/2018 - 02:41

SUM function for your search volume will be:

Excelchat Expert
02/04/2018 - 02:42

=SUM($D$2:$D$270)

Excelchat Expert
02/04/2018 - 02:42

You can use this function anywhere on your sheet.

Excelchat Expert
02/04/2018 - 02:42

Now if you copy the formula across, it will not change.

Excelchat Expert
02/04/2018 - 02:43

I understand that your worksheet is part of a study assignment.

User
02/04/2018 - 02:43

thats correct

Excelchat Expert
02/04/2018 - 02:44

It would have been good if you could have explained the context. I know that's time consuming, but I could have helped you better then.

User
02/04/2018 - 02:44

so what might be 2 other fuctions I might use

Excelchat Expert
02/04/2018 - 02:44

I need to understand the problem statement first, only then I can write the formulas.

User
02/04/2018 - 02:44

if you want I can give you the whole instruction set

Excelchat Expert
02/04/2018 - 02:44

Yeah, that would be better.

User
02/04/2018 - 02:44

so you can know where we came from

User
02/04/2018 - 02:45

◦Create a nicely formatted table with headings
◦Freeze the table headings so that they are always in the viewing panel
◦Conditional format the Top 10% and Bottom 10% of one numerical column
◦Bar format one numerical column
◦Icon format one numerical column and specify your own rules
◦Make a Pivot Table on a new sheet and use Report Filtering
◦Nicely format a chart with data labels, title, axes labels and colored branded to your start-up on a new sheet
◦Include at least three functions, properly labeled
◦Include an additional function or formula that has at least one reference locked so that if it is dragged-and-dropped the reference does not change
◦On a separate sheet in your Excel file labeled ‘Results’, make 3-5 bullet observations concerning this business, based on your analysis and review of the data

Excelchat Expert
02/04/2018 - 02:46

Ok.

Excelchat Expert
02/04/2018 - 02:46

I believe you have done the most.

Excelchat Expert
02/04/2018 - 02:47

How did you calculate Traffic (%)

Excelchat Expert
02/04/2018 - 02:47

You did it manually?

Excelchat Expert
02/04/2018 - 02:47

or was it there before?

User
02/04/2018 - 02:47

ok

User
02/04/2018 - 02:48

it was there before

Excelchat Expert
02/04/2018 - 02:48

Ok. So what columns you added in this?

User
02/04/2018 - 02:48

so we have 1 formula

User
02/04/2018 - 02:48

I need 2 more correct?

User
02/04/2018 - 02:49

did you apply it to the page or do I need to?

Excelchat Expert
02/04/2018 - 02:49

No you need only 1 function that has absolute reference.

User
02/04/2018 - 02:49

I am sorry this is my first time ever using excel

Excelchat Expert
02/04/2018 - 02:49

You need to apply to the page.

User
02/04/2018 - 02:50

nclude at least three functions, properly labeled

User
02/04/2018 - 02:50

include

Excelchat Expert
02/04/2018 - 02:51

I have given the solution of this one: nclude an additional function or formula that has at least one reference locked so that if it is dragged-and-dropped the reference does not change

Excelchat Expert
02/04/2018 - 02:51

And yes, you need two more functions.

Excelchat Expert
02/04/2018 - 02:52

One you can use is =MAX(E2:E270)

Excelchat Expert
02/04/2018 - 02:52

That will give you highest CPC

Excelchat Expert
02/04/2018 - 02:52

The other one will be =MIN(E2:E270), and this will give you lowest CPC.

Excelchat Expert
02/04/2018 - 02:52

You can use similar function for Search volume.

Excelchat Expert
02/04/2018 - 02:53

For example, =MAX(D2:D270) will give you highest search volume.

Excelchat Expert
02/04/2018 - 02:53

I hope that will help you.

Excelchat Expert
02/04/2018 - 02:53

There?

User
02/04/2018 - 02:54

it does how do I label those?

User
02/04/2018 - 02:54

do I need to make those functions or have you already done that

Excelchat Expert
02/04/2018 - 02:54

Ok. Let me do it on your sheet.

User
02/04/2018 - 02:55

I made a formulas page if that helps

Excelchat Expert
02/04/2018 - 02:56

I have done that in formula page. Please see that.

User
02/04/2018 - 02:57

where do I see that at?

Excelchat Expert
02/04/2018 - 02:57

On your Formula page

User
02/04/2018 - 02:58

the formulas or the labels

Excelchat Expert
02/04/2018 - 02:58

See column O and P.

Excelchat Expert
02/04/2018 - 02:58

Both are there.

Excelchat Expert
02/04/2018 - 02:58

O has labels, and P has formula

User
02/04/2018 - 02:59

gotcha I see them and the one I need to apply to the whole page or that is already done?

Excelchat Expert
02/04/2018 - 02:59

It's already done.

User
02/04/2018 - 03:00

you are the man!!! thank you for all your help. that finishes everything I need correct?

Excelchat Expert
02/04/2018 - 03:00

Yes. You need to give the analysis though. That's the last part.

User
02/04/2018 - 03:01

or woman I dont know if you are a woman no offense if you are

User
02/04/2018 - 03:01

I apologize

Excelchat Expert
02/04/2018 - 03:01

Ha ha. No worries. It's gender neutral platform. We all are human beings.

Excelchat Expert
02/04/2018 - 03:01

Not a bot for sure ;)

User
02/04/2018 - 03:01

I am about to make the analysis right now thank you for all your help

Excelchat Expert
02/04/2018 - 03:01

I am glad that I could help you.

Excelchat Expert
02/04/2018 - 03:02

Hope to see you here again soon.

Excelchat Expert
02/04/2018 - 03:02

You can end this session now by clicking 'END' button above.

Excelchat Expert
02/04/2018 - 03:02

Please don't forget to rate your experience.

User
02/04/2018 - 03:02

its my first time doing excel and I am honestly very confused and lost about this whole thing I am glad i found your site

Excelchat Expert
02/04/2018 - 03:02

Have a nice day ahead!

User
02/04/2018 - 03:02

for sure you get 5 stars

Excelchat Expert
02/04/2018 - 03:02

That's great. Happy to help you in learning Excel.

User
02/04/2018 - 03:03

definitely

Excelchat Expert
02/04/2018 - 03:03

Bye!

User
02/04/2018 - 03:03

bye

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*