If Statement for Increase/Decrease of Numbers with a calculation & some coloring

[cateyez86]

Hello Everyone! I am new here, but I have used many of the formulas people have posted for other projects I have made. I am trying to create a spreadsheet for the formulas for 'The Marketing Game!' simulation, and there is an area where I have to do a complex IFSTATEMENT. I am needing to take 2 numbers, find their change, indicate if it was an increase, decrease, or no change (without it saying that), and to have it do calculations based on the increase, decrease, or no change. I know, this is a ton, but it will speed my process up later on since I have to do this every week for 13 weeks.

Here's what I'm working with:

Special Commands (5-20): Before - 8 After - 11 Change - Went up 3 Increase: Take change# * $4 Decrease/No Change: $0 indicated
Error Protection (1-10): Before - 3 After - 5 Change - Went up 2 Increase: Take change# * $3 Decrease/No Change: $0 indicated
Ease of Learning (1-10): Before - 5 After - 4 Change - Went down 1 Increase: Take change# * $2 Decrease/No Change: $0 indicated

This is the formula I have to start with, but unsure where to go from here. I don't need it to say Increase, Decrease, or No Change, I just need it to do the calculation needed.

=IF(C12>B12,"Increase",IF(C12<B12,"Decrease","No Change"))

C12 = After # for Special Commands
B12 = Before # for Special Commands


For the Before and After columns, I'd also like to have something stating that if that number is not within the range I have in the parenthesis above, it will change its color or alert me so I'm aware it's out of range. What can I do for that? Thank you all in advanced!

[FDibbins]

This is the formula I have to start with, but unsure where to go from here. I don't need it to say Increase, Decrease, or No Change, I just need it to do the calculation needed.

=IF(C12>B12,"Increase",IF(C12<B12,"Decrease","No Change"))

All you need to do is put the different calcs where you have the text. For example....
This is the formula I have to start with, but unsure where to go from here. I don't need it to say Increase, Decrease, or No Change, I just need it to do the calculation needed.

=IF(C12>B12,10*10,IF(C12<B12,10*5,5*5))

Not sure what your "special commands" are though?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[cateyez86]

Here is what I have at the moment. Don't mind the extra junk at the bottom, those are just my notes I pulled from the reading. ha.

Rows 12-14 are where I am working. I have the change between the 2 numbers, but I need to know if it was an increase or decrease, then have it do the math based on if its a decrease or increase. With the before/after section on those same lines, I need to know if they are between the range numbers or not as well. I would like something to visually show or tell me it's not in the range.

With this, I get charged for increasing, but I do not get charged anything if I decrease or stay the same.

Marketing Game - Formulas Sheet.xlsx

[FDibbins]

If you decrease, your answer will be negative, if you increase it will be positive, else it will be 0.

With that in mine, you can construct a formula that says something like...
=IF(D12<0,use-this-formula,IF(D12>0,use-that-formula,else-do-this))
<0 would be for negative
>0 would be for positive
if neither of these, then it must be 0

I need to know if they are between the range numbers or not

If this means between 5-20 (Special Commands (5-20)) then you will need to pull those range values from the text - you cannot check < or > with text (is apple bigger than Peach?)
Also, not sure if you want to test for before, or after?

Assume After...
=IF(AND(C12>=--MID(A12,FIND("(",A12,1)+1,1),C12<=--MID(A12,FIND("-",A12,1)+1,2)),"Yes","No")
Not sure where you want to put this, but you would then replace "Yes" and "No" with whatever you need to happen, based on the results

[cateyez86]

All you need to do is put the different calcs where you have the text. For example....
This is the formula I have to start with, but unsure where to go from here. I don't need it to say Increase, Decrease, or No Change, I just need it to do the calculation needed.

=IF(C12>B12,10*10,IF(C12<B12,10*5,5*5))

Not sure what your "special commands" are though?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I tweaked this a little and it seemed to work. Thank you hun!

[FDibbins]

Happy to help