Expert Solution - Excel IF Problems

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

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.