Need an Excel Formula with a few variables to consider?

Question

I will try to explain it as clear as I can. This is what i need the formula to work out:

If (F1) is between ($0 and $100,000) and (D1) is equal to or larger than (E1) then = yes (yes in big green letters, else 'no' in big red letters)

the D1, E1, and F1 cells will have dollar amounts in them.

What i also need if for the range to change in incriments as well:
0-100000,100000-150000,150000-300000,300000-550000,550000-900000,900000-1500000

Any help will be much appreciated!

Answer 1

Here is the formula:

=IF(AND(F1>0, F1<=100000, D1>=E1), "YES", "NO")

- when you say between 0 and 100000, I assume you mean greater than 0, and less than or equal to 100000, but you can adjust that

- what if D1 and/or E1 are 0 or blank -- is this possible? does it matter? if so we'll need to manage this in the formula.

In order to make the Yes green and the No red, you need conditional formatting.
- click on the formula and do Format - Conditional Formatting
- in the first box, leave as "Cell Value Is"
- in the second box, change to "equal to"
- in the third box, put "YES"
- click on the Format button and change the font color, and OK
- click on the Add button to set the format for NO in a similar way

As for the range changing in increments, I'm not sure what you mean by that -- can you clarify?

Cheers.
.

Answer 2

Aladou is on the right track

what you want to do to incrementally change the values is to reference a cell instead of the figure in the equation. Instead of

f1>0,f1<10000000) etc.

f1>a1,f1
If you are asking to increment within the formula you would need to stack the if statements

if(aladou formula,"yes",if(aladou formula+increment,"yes",no))

You are only allowed to stack 7 if() statements. Try to figure out if you have an absolute and set everything else to default "Yes" or "No". This saves a lot of time.

God Bless

Frank Pytel

Answer 3

ask these question in a excel user group or forum
do a web search for excel user groups and forums.

like

excel+user groups+forums