Growth Calculation Formula Not Working

[dzarcone]

Hello! I need help as I can't figure out what is wrong in my formula.

I'm wanting a ranking to show up based on current year sales compared to prior year sales. If <50% of last year, I want it to say "High" but can't get it to show up. Here's an image showing my formulas. You'll see that, in this example, my sales are less than 50% of last year and risk is showing as "moderate". Hmmm ......

Excel Formula Help.jpg

Appreciate your help!

[Sam Capricci]

reverse your order because an if statement stops when it encounters the first item that satisfies the criteria and so the E6 as less than -25% meets that criteria with the -97%
something like...
=IF(E6>0,"Low",IF(E6<-50%,"high",IF(E6<-25%,"moderate","need info")))

of course with the one I posted, it won't know what to do with -17%, it will return need info so you may have to rearrange some of the ranges.

[dzarcone]

Sam C, You Rock! Can't believe it was that easy after spending over an hour trying to figure that out!

One more quick question ... In another cell I have this formula: =IF(J6>50%,"Low",IF(J6>=0.21,"Moderate",IF(J6>0,"High","")))

However, when I copy it down the column and there is no value in K6, L6, etc. the value showing up in the cell is "Low". How do I get this to show up as blank until a value calculated in K6, L6, etc.??

[Sam Capricci]

Not sure I'm following, if you drag it down the column as written it should index to J7 then J8, then J9 etc.
if you drag it toward the right it would index to K6, then L6 etc.
It is interpreting the "low" from J6 or K6 or L6 (if they would've been empty) as having something in them so it is returning the first item in the if statement.
maybe try this instead... =IF(OR(J6="",J6="low"),"",IF(J6>50%,"Low",IF(J6>=0.21,"moderate",IF(J6>0,"high",""))))

[dzarcone]

So here is my issue .... When I copy the formula to the other rows in that column, it's coming up with a value "Low" but there isn't any data in J7 or J8 so why is it pulling the value ot "Low" versus leaving it blank?? Is there a way to make it blank if nothing is in J7 or J8, for instance??

Current formula: =IF(J7>50%,"Low",IF(J7>=0.21,"Moderate",IF(J7>0,"High","")))

Attachment 618898

[Sam Capricci]

when I click on your attachment I get "invalid attachment". To attach a workbook go advanced, then midway down the sheet click on manage attachment, browse to a workbook and upload it to the forum, click ok, and submit reply. You are sort of mixing different values. In the first part you have if J7 is greater than 50%, return Low, then you have if J7 is greater than or equal to 0.21 which I am interpreting as 21% to return moderate, then if J7 is greater than 0 return high. If I put 1000 (no percent) in J7 it still returns low. What is it you are trying to accomplish?
If you can upload a workbook with just enough examples AND expected results then I (or someone else on here) could give you better help.

[dzarcone]

Thanks, Sambo Kid!

I'm attaching a spreadsheet to show you what I'm trying to do. Appreciate any help you can give me! Thanks in advance!

[Sam Capricci]

just had a chance to get back to this after a couple days away, I don't see anything wrong with the formula in cell F4 of your customer visit analysis sheet based on your goals written in column K, the formula appears to return the results based on those notes.

[dzarcone]

How do I set it up so that nothing shows in column F if no data has been selected in columns C & D and hence, no growth rate has been calculated in column E?

[Sam Capricci]

one of two ways, if E4 remains empty then you can use this...
=IF(E4="","",IF(E4>0,"Very Low",IF(E4<=-50%,"High",IF(E4<=-25%,"Moderate",IF(E4=0,"Low",IF(E4<-1%,"Low",""))))))
But if you copy down the formula in E4 into E5 but nothing is in C5 or D5 then you'll have a divide by zero error so then I'd use this instead...
=IFERROR(IF(E4="","",IF(E4>0,"Very Low",IF(E4<=-50%,"High",IF(E4<=-25%,"Moderate",IF(E4=0,"Low",IF(E4<-1%,"Low","")))))),"")
Hope that helps.

EDIT: oh, and you can get rid of the divide by error in col E when dragged down by changing the formula in E4 to this... =IFERROR((D4/C4)-1,"")

[dzarcone]

Hey Sambo! Thanks so much for your help! Greatly appreciated!