Formula help please!! IF(OR

[Peet]

Hi,
I am trying to use this formula below to calculate costs based on reference
letters, but now want to exclude some of the letters used in it so it doesn't
include certain costs in to the equation.

The problem is it is only returning a result of zero. Any ideas why this
is happening?

=IF(OR('Exec Sum'!D11="C",'Exec Sum'!D11="L",'Exec
Sum'!D11="M"),0,LOOKUP($F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M"},'Market
Summary'!Q9:Q21)+IF(OR('Exec Sum'!D12="C",'Exec Sum'!D12="L",'Exec
Sum'!D12="M"),0,LOOKUP($F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M"},'Market Summary'!$Q$9:$Q$21)+IF..................

thanks for looking!

 

[Jerry W. Lewis]

It is not possible to fully debug an incomplete formula. Copy the entire
formula from the formula bar and paste it into the your post (do not retype,
as you may introduce subtle differences).

When 'Exec Sum'!D11 contains something other than "C", "L", or "M" do you
get the (nonzero) contents of the appropriate cell from 'Market
Summary'!Q9:Q21 ?
If not, is there a circular reference somewhere in your workbook that is
preventing recalculation? For that matter, check your options
(Tools|Options|Calculation in versions prior to 2007) to be certain that
Automatic is selected.

Jerry

 

[T. Valko]

You're missing closing ) at the end of each IF statement.

You can greatly reduce the length of the formula if you were to use a lookup
table rather than hardcode your lookup values. You already have the numeric
values in a list so why not include the lookup values in the list?

If you did that your formula could be as simple as:

=IF(OR(D11={"C","L","M"}),0,SUMIF(P921,F11,Q9:Q21))