# Help with Excel formulas

I have attached the sheet I am using and there are formulas that I don't understand. Can anyone please help out and tell me what they mean?

=\$C\$8*(1+C9)

=IFERROR(J16/\$K\$14,0)

=IF(\$K16<>"",(D16/100)/\$K16,"")

=IF(SUM(N16:R16)>0,AVERAGE(N16:R16),IF(SUM(C16:R16)>0,AVERAGE(C16:R16),0))

=IF(S16<>0,RANK(S16,\$S\$16:\$S\$53),"")
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
>I have attached the sheet I am using

=\$C\$8*(1+C9)
Multiple absolute cell reference C8 by the result of adding 1 to the value of cell C9 (relative cell reference )

=IFERROR(J16/\$K\$14,0)
If dividing 16 by the value of K14 results in an error value, use 0 as the result

=IF(\$K16<>"",(D16/100)/\$K16,"")
If K16 is not empty, divide the result of D16 over 100 by K16, otherwise use empty string("") as the result

=IF(SUM(N16:R16)>0,AVERAGE(N16:R16),IF(SUM(C16:R16)>0,AVERAGE(C16:R16),0))
If the sum of range N16:R16 is greater than zero, get the average, otherwise, get the average of C16:R16 if the sum of that range is greater than zero. If neither range is greater than zero, return zero as the result.

=IF(S16<>0,RANK(S16,\$S\$16:\$S\$53),"")
If S16 is not equal to zero, get the rank of that value within the range from S16:S53, otherwise, use empty string as the result.
0
Author Commented:
I did attach it but it did not show up. Anyways, here is another link:

This sheet needs to be passed around to several people who will be working on it and they will look up the formula and will get confused by it. I need to simplify this and create a much simpler formulas to do the same thing. I would really appreciate some help here.
0
Commented:
I'd suggest that rather than attempt to simplify the formulas, which are not over-complex, you add the explanatory text (such as I provided above) in comments on the sheet.

If you simplify the formulae, you'd have to add more cells/rows/columns to the sheet to achieve the same end result.
0
Author Commented:
Ok. I don't quite understand the sheet myself. If you could explain it to me, I can add in my own input to it and explain it to everyone else.

Thanks
0
Commented:
Perhaps you should start by explaining the purpose of the calculations.
0
Author Commented:
This is an attribution model. As you can see in the sheet (link), the purpose it to assign a rank and measure the effectiveness of each channel/site we are advertising on based on different attribution models.
0
Commented:
One simplification that you might consider is making use of named ranges in place of absolute references. This can result in formulae that are easier to make sense of.
0
Commented:
Attached is an example for the first case in your list. Cell C8 is named BaseThreshold and cell C9 is named GreenThreshold. The formula in cell D9 now reads
=BaseThreshold*(100%+GreenThreshold)
I also changed the 1 to 100%, which has no mathematical impact but makes it clearer that you're working with a percentage.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.