Formula to return under-budget store locations

[BeckyRode]

Hi folks... I hope this will be simple and thank you in advance for your help.

Each month I get a 1400 row Excel sheet with each of our retail store
locations on it (1 row per store). It’s got a lot of columns in it, but the
one I’m concerned with is the number of sales calls received that month.

Each store belongs to a chain, and each chain has a budget for sales calls.
What we have to do is pick out is locations that received less than their
budgeted sales calls. So, for example, a store that should have gotten 6
calls and only got 4. Usually this is done by hand and with 1400 lines, you
can imagine, is very time consuming even with filters.

I want all under-budget stores to automatically get plopped onto their own
sheet, section, or tab, preferably by chain. My trouble is that each of the
chains has a different budget.

So, in a nutshell…

Account G’s budget is 8 calls. For all Account G’s locations, if calls < 8,
then have that location’s row show up over here (or otherwise get separated
out). Repeat for the other four accounts with their budgets.

A scaled down version of the sheet:

Chain StoreNumber SalesCalls
Petstore A 1352 6
Petstore A 1022 9
Petstore B 152 5
Petstore C 2077 10

A's budget is 8 calls, B's budget is 7, C's budget is 6. I'd want something
that would pull out the first and third store in this mini-example.

Hopefully that's clear. Thanks again for your help!

 

[Josh Craig]

Do you have the budgeted sales calls in a column?

Here's an imperfect way to do it with an IF statement:

Try =IF(C2>D2,"OVER BUDGET",IF(C2=D2,"ON BUDGET","UNDER BUDGET")) in cell E2
and fill down.

expected results

column A: Column B: Column C: Column D:
Column E:
Chain StoreNumber SalesCalls BudgetedCalls
Budget?
Petstore A 1352 6 8
UNDER
Petstore A 1022 9 8
OVER
Petstore B 152 5 7
UNDER
Petstore C 2077 10 6
OVER

You could then just filter for UNDER BUDGET.

I think the easiest way to get the cells on another sheet would be to record
a macro of copying and pasting the filtered cells.

I hope this is the kind of thing you were after.

 

[BeckyRode]

Unfortunately the budgeted sales calls aren't in a column on this sheet. Is
there a way to plug in something to your suggestion to make it work?

Perhaps something like "if Chain=PetstoreA and SalesCalls< 8, "Under budget"
for each chain, with the appropriate salescalls number?

Thanks for the quick response the first time, and thank you for your help!

 

[Josh Craig]

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).

 

[BeckyRode]

There are five chains to track, so it looks as if the VLOOKUP is the way to
go. If it's not too much trouble, any help with creating a table and using
the function to find the under-budget stores would be very, very much
appreciated.

Thanks again!!!

 

[Josh Craig]

Okay, first on a new sheet (Sheet2 in my example) create a table with the
chain names and budgets:

column a: column b:
petstorea 8
petstoreb 7
petstorec 6
petstored 5
petstoree 4

then use this formula in a column on Sheet1:
=IF(C2<VLOOKUP(A2,Sheet2!A$1:B$5,2), "UNDER BUDGET","OVER OR ON BUDGET")

Then fill down.

The VLOOKUP command tells excel to find the value from A2 in the table on
Sheet2 and return the value from column 2 on Sheet2. Then it's just a matter
of comparing this value to actual phone calls (C2 in my example)..

Hope that all works. Please click 'yes' below if I've answered your question.

 

[Josh Craig]

oops! one modification- the formula should be:

=IF(C2<VLOOKUP(A2,Sheet2!A$1:B$5,2,FALSE), "UNDER BUDGET","OVER OR ON BUDGET")