Whats Wrong with this Formula?

[John]

Can anyone help me with what is wrong with this formula, my eyes have given
up looking at it

Thanks

=IF(COUNTIF(Restaurant_Names_inQuery,"="
&$A7)<1,"-",SUMIF(Restaurant_Names_inQuery,"="&$A7,sum(Food_Costs_inQuery)+(
(FoodINGR_Costs_inQuery)))/SUMIF(Restaurant_Names_inQuery,"="&$A7,Sales_Reve
nue_inQuery))))

 

[Peo Sjoblom]

First of all this is not an error but not necessary, you can replace

"="&$A7

with

,$A7

in both the sumif and the countif function, there won't be any difference
but they are superfluous

the first SUMIF does not have a closing parens, should be

SUMIF(Restaurant_Names_inQuery,A7)

then I assume you should add what comes next to the sumif so

SUMIF(Restaurant_Names_inQuery,A7)+(SUM(Food_Costs_inQuery,
FoodINGR_Costs_inQuery)

then divide


SUMIF(Restaurant_Names_inQuery,A7)+(SUM(Food_Costs_inQuery,
FoodINGR_Costs_inQuery)/SUMIF(Restaurant_Names_inQuery,$A7,Sales_Revenue_inQ
uery))

could be changed to

SUM(SUMIF(Restaurant_Names_inQuery,A7),SUM(Food_Costs_inQuery,
FoodINGR_Costs_inQuery)/SUMIF(Restaurant_Names_inQuery,$A7,Sales_Revenue_inQ
uery))

so maybe (not tested)

=IF(COUNTIF(Restaurant_Names_inQuery,$A7)<1,"-",
SUM(SUMIF(Restaurant_Names_inQuery,A7),Food_Costs_inQuery,
FoodINGR_Costs_inQuery)/SUMIF(Restaurant_Names_inQuery,$A7,Sales_Revenue_inQ
uery))














--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

First of all this is not an error but not necessary, you can replace

"="&$A7

with

,$A7

in both the sumif and the countif function, there won't be any difference
but they are superfluous

...

Not true when A7 is blank or evaluates to "". Then these two criteria *will*
give different results if the range in the first argument contains any blank
cells or cells evaluating to "".

 

[John]

Thanks Guys, Peo your last formula 'looks' like what I need, however it
produces an inaccurate result, based on a 'long hand' check. Maybe I should
give a little more detail.

The following formula works

=IF(COUNTIF(Restaurant_Names_inQuery,"="&$A7)<1,"-",SUMIF(Restaurant_Names_i
nQuery,"="&$A7,Food_Costs_inQuery)/SUMIF(Restaurant_Names_inQuery,"="&$A7,Sa
les_Reve
nue_inQuery))

I am trying just to ADD the Range Name "FoodING_Costs_inQuery TO
Food_Costs_inQuery, this seems so simple, normally Excel suggests to you if
you need another Parentheses but all I'm getting is "The Formula you Typed
contains an error". I can add the two ranges together in a separate cell,
but the syntax of including it within the formula above has beaten me

Don't know if it makes any difference but the Range names above are on
different worksheets, maybe the COUNTIF Restaurant_Names_inQuery,"="&$A7 is
causing a problem, wouldn't have thought so.

Thanks