Excel 2007 : If statement help

[Batman11692003]

I am trying to write an if statement to check 4 numbers to see if they are greater than or equal to each other.

A. B. C. D
11 22. 10. 15
27. 50. 40. 50

So far I got if(and(a1>=d1,b1>=d1),"red",if(or(a1>=c1,b1>=c1,((b1-a1)*a1))

But when I copy the code to the second line, I need it to say red but the calculations still shows up since b1>=c1.

Can anyone help me please

[NBVC]

Did you mean?

=IF(AND(A1>=D1,B1>=D1),"red",IF(OR(A1>=C1,B1>=C1),(B1-A1)*A1))

how is second supposed to be "red"? Should you have an OR instead of an AND>

[Batman11692003]

I would like the second row to be red since it meets the first agrument of b2>=d2

[NBVC]

So then you want OR, not AND...

Try:

=IF(OR(A1>=D1,B1>=D1),"red",IF(OR(A1>=C1,B1>=C1),(B1-A1)*A1))

[Batman11692003]

Sorry but I forgot to add, for the same example, if a1>=c1 or b2>=c1 I want it to perform the calculation b1-a1*a1. But if at any point like b2>=d2 then I want the code to say red. Thanks

[NBVC]

Do you mean?

=IF(OR(A1>=D1,B1>=D1,B2>=D2),"red",IF(OR(A1>=C1,B1>=C1),(B1-A1)*A1))

[Batman11692003]

But I also what to calculate/show the value if a1>=c1. But I don't want to see it when b2>=d2

[NBVC]

Don't know what you mean... did you try the formula.

If B2>=D2, it will be "red", and if B2<D2 and A1>=C1, you will get a value.

If you disagree or have issues, post a sample workbook with various samples and expected results, and re-state the full conditions from scratch in words.

[Batman11692003]

Here is an excel file to show you want I am trying to do

[NBVC]

Try:

=IF(OR(A2>=D2,B2>=D2),"red",IF(OR(A2>=C2,B2>=C2),(B2-A2)*A2))

[Batman11692003]

It works for row 2 but it shows up red for the first line. But I want te calculations to show. Since If the box color is yellow and red, or just yellow I want the calculation to show up still.

[NBVC]

You can't calculate based on cell colours... only cell contents.

[Batman11692003]

I just did that to show you want I am looking for. Also when I change it to and for the first if statement and or for the second if statement. The calculations shows for the first row but then it doesn't say red for the second row

[NBVC]

You know this should be very simple, yet I am having a hard time understanding your requirements or we fell onto different pages at some point.

Can we start at the beginning and just try articulating your requirements in words.

[Batman11692003]

I am trying to find the total of how much I need from stock or the warehouse. So if number sold is greater than stock or peak sold is greater than stock. I would for excel to run the calculation

But if number sold is greater than or equal to warehouse or if peak sold is greater than or equal to warehouse I just want it to say red

But if # sold or peak sold is greater than or equal to stock I want the calculation to show up. But if it's on the same line like the B2 in te excel file where there is a red and yellow. I still want the calculations to show up.

I hope I explained it clear enough for you and thanks for your help already

[NBVC]

This is the part that is eluding me.

But if it's on the same line like the B2 in te excel file where there is a red and yellow. I still want the calculations to show up.

You can't compare colours, so what makes row 2 calculate an answer, and row 3 to return "Red", ignoring the cell colouring?

[Batman11692003]

For row 2 since # sold is higher than stock I would like to do the calculation to find how much more I need. And for row 3, since peak sold is greater than or equal to warehouse I would like the if statement to say red when that occurs.

But let's say if there was a new row added,
A. B C. D
49 60 48. 62

Now a1>=c1 and b1>=c1 so I would want the calculation to show for that new row

[NBVC]

Is this any closer?

=IF(A2>=C2,(B2-A2)*A2,IF(OR(A2>=D2,B2>=D2),"red",IF(B2>=C2,(B2-A2)*A2,"")))

[Batman11692003]

Thank you. It seems like it's working, but I have to play around with it a little more. Since some columns have Out, so when I run the if statement it says #value!

[Batman11692003]

Also what if a and b are both true
For example
A. B. C. D
49. 60. 48. 61

I tried to add an AND Statement at the end if the last if statement but it still didn't work.

[NBVC]

Don't know what that means.

[Batman11692003]

Sorry. I figured it out. But my question is how do I sum up the values only in the column. Since some say RED and some have #value!.

[NBVC]

Try:

=SUMIF(E2:E6,"<="&9.99999999E+307)

adjusting range to suit.