Formula for Ratios

[Guest]

Hello,

I am working on a spreadsheet that will be used to calculate financial
ratios. I have 2 worksheets; one is named data and contains the company name,
year, and the values for certain financial information. An example of the
data worksheet looks like this:
Name RepDate GrossProfit NetSales
Company1 2003 5,000 10,000
Company2 2004 7,000 15,000

The 2nd worksheet is named Ratios and contains the actual ratio name. An
example of how it looks is this:
Gross Profit Margin Ratio 2003 2004
Company1
Company2
I need help in creating a formula that will look at the comany name and the
RepDate (which is the year) and then get the appropriate values from the data
sheet to calculate the ratio needed. For example the Gross Profit Margin
Ratio is Gross Profit / Net sales. How do I get a formula to say IF the name
= Company1 and the RepDate = 2003 then get me the value for the 2003 Gross
Profit from the data sheet and divide that by the value for the Net Sales
from the data sheet. Would this need to be an array formula. I hope I
explained this properly; if not please ask for more info and I will reply.
Any help with this issue will be greatly appreciated. Thank you.
Dave Y
PS- I previously posted this same issue a little while ago but I received a
"Page cannot be displayed" error. I apologize if this post appears twice.

 

[Biff]

Hi!

Here's one way:

Assume this table:

Name RepDate GrossProfit NetSales
Company1 2003 5,000 10,000
Company2 2004 7,000 15,000

is on sheet Data!A13

This table:

2003 2004
Company1
Company2

is on sheet Ratio!A1:C3

In Ratio!B2 enter this formula:

=SUMPRODUCT(--(Data!$A$2:$A$3=$A2),--(Data!$B$2:$B$3=B$1),Data!$C$2:$C$3)/SUMPRODUCT(--(Data!$A$2:$A$3=$A2),--(Data!$B$2:$B$3=B$1),Data!$D$2:$D$3)

Copy across to C2 then down.

Now, based on your limited sample data Ratio!C2 and Ratio!B3 will return
#DIV/0! errors because there is no matching data for Company1 2004 and
Company2 2003. If you don't want to see the errors you can build an error
trap into the formula. This will make the formula even longer than it is
already. An alternative method is to "hide" the errors using conditional
formatting. This method can lead to "problems" in downstream calculations if
you're not aware that the error values are still in the cells but you just
can't see them.

Biff