Formula to return under-budget store locations
If there's only three chains i.e. petstore a, b and c then you could use this
formula in column D:
=IF(A2="PetstoreA",IF(C2<8,"UNDER BUDGET","ON OR OVER
BUDGET"),IF(A2="Petstoreb",IF(C2<7,"UNDER BUDGET","ON OR OVER BUDGET"),
IF(C2<6, "UNDER BUDGET","ON OR OVER BUDGET")))
If there's more this won't work because excel won't let you use any more IF
statements in the one function.
You'll have to either create a column and enter in the budgeted calls or
(probably better) create a table with chain name and budgeted calls and then
use vlookup.
This isn't a very complicated thing to do but let me know if the first
formula is sufficient before I launch into an explanation of VLOOKUP (or
maybe you can work it out yourself).
|