# Excel formula

#### ridwanr

##### New Member
Hi all,

Am looking for an excel formula in which the Cost Pub and Cost per Contract if MESSI and Rooney is 0 and #DIV/0! then take the (column C) figues that is Total Cost as the answer.

Pls see attached file for better understanding.

Regards,
Ridwan

#### Attachments

• 11.4 KB Views: 3

#### herofox

##### Active Member
hello-You Can To Use This Formula Start From Cell F2
Code:
``=IF(AND(\$A2="Messi",\$B2=0),\$C2,IF(AND(\$A2="Rooney",\$B2=0),\$C2,(C2+D2)/B2))``

#### Attachments

• 12.1 KB Views: 6

#### pecoflyer

##### Active Member
A bit shorter perhaps
=IF(AND(OR(\$A2={"Messi";"Rooney"}),\$B2=0),\$C2,(C2+D2)/B2))
I hope the array separator is correct?

#### bosco_yip

##### Excel Ninja
This short formula can return the same result

In F2 formula copied down :

=IF(B2,(C2+D2)/B2,C2)

#### Peter Bartholomew

##### Well-Known Member
Same as @bosco_yip but, for me it is a dynamic array formula
= IF( NumContracts=0, TotalCost, (TotalCost+CostPerDay)/NumContracts )
The condition NumContracts=0 is more verbose but allows blank cells.