Help with formula

[rwe1187]

Let's say I have cells E5 - E10 and F5 - F10. I want a message to appear in cell F11 that basically says "if the value in any cell E5-E10 is <> 8 and E5 - E10 are blank then display message 'check cells' but of E5 - E10 = 8 then cells F5 - F10 can be blank but if the values in cells E5 - E10 are <> 8 and there is any text in F5 - F10 then cell F11 is blank"... I can get the formula to work in individual cells H5 - H10 and I can get it to work in F11 for just one cell in the noted range, but I cannot get it to work for all cell ranges and display in one cell.

 

[rwe1187]

Yes but if you want one cell with an error, what should happen if some of the rows are ok & some are not?

I have zero idea how to explain it anymore, and I cannot upload the form with sensitive info and it would take too long to delete all of that. I edited one page and copied it.
So E6 is greater than 8. E5 is 8. So if E6 is greater than or less than 8 and F6 is blank I want an error message in F11.
So if E6 is greater than or less than 8 and F6 has text then I do not need an error message in F11.
If E6 and F6 are blank then nothing in F11.

Do the exact same for e7....and e8....and e9....placing the error message in F11...
There is a calculation in E6 that calculates the difference between c6 and d6.

 

[rwe1187]

This is not close, I posted a pic of the spreadsheet with what I was saying and now it is gone. So thanks for your help, but it is not working, the post disappeared and I am lost and will have to find someone or somewhere else to help me. I cannot believe that the last post I put up is gone. And now it is back. Insanity.

 

[Fluff]

Your last post did not disappear, it's just that it's the 1st post on the 2nd page of the thread. It catches a lot of people out until they get used to the board.

I fully understand what the criteria, but you are not answering my question. In you image both row 5 & 6 & the rest are blank, so no message.
But what if E7 becomes 9 & F7 is blank? Do you want the error message to appear in F11?
If so please try the last formula I posted, rather than just dismissing it out of hand, because you don't think it will work.

 

[rwe1187]

Your last post did not disappear, it's just that it's the 1st post on the 2nd page of the thread. It catches a lot of people out until they get used to the board.

I fully understand what the criteria, but you are not answering my question. In you image both row 5 & 6 & the rest are blank, so no message.
But what if E7 becomes 9 & F7 is blank? Do you want the error message to appear in F11?
If so please try the last formula I posted, rather than just dismissing it out of hand, because you don't think it will work.

NVM. I will try someone else or another site. You are using the SUM formula which has 0 to do with what I am trying to accomplish. I have been at it for 8 hours and there is no way it should take this long. I can get the equation to work in each row, but I do not want a row full of equations. But that is what i will have to do. Thanks.

 

[Fluff]

I will try someone else or another site.

There is no need, I am more than happy to help. But if you do please ensure that you include any cross post links on both sites.

You are using the SUM formula which has 0 to do with what I am trying to accomplish.

Have you actually tries the formula I suggested, or just dismissed it out of hand because you don't think it's what you want?

I can get the equation to work in each row, but I do not want a row full of equations.

There is no need for that. Can you please answer the question that I have repeatedly asked you?

 

[rwe1187]

Yes. I did try the formula. I am going to try this a simpler way. See if this helps. I tried the mini sheet thing along with a pic.

BookB.xlsx
	A	B	C	D	E	F	G
1
2			Score1	Score2	Total Score	Reason
3
4		me	8	4	12	I won
5		you	6	6	12		no reason
6		he	1	0	1		no reason
7		her	-1	0	-1		no reason
8		they			0
9
10
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
E4:E8	E4	=SUM(C4:D4)
G4:G8	G4	=IF(AND(E4<>0,F4=""),"no reason","")

Cells with Data Validation
Cell	Allow	Criteria
F4:F8	List	I won,You won,He won,She won,They won

So I tried the mini page thing and a copy. Cells e4,e5,e6,e7,e8 calculate the c and d cells. Then you choose a reason in cell f. In cell g, if you see row 4 added, the score is >0 then a reason is chosen from the drop down menu. Cell G is blank. ALl good. Row 5 is added and cell e5 >0 but no reason is chosen, so in cell G an error pops up. Same for row 6 and 7. Row 8 there is no scores, so the total score is zero so nothing happens. Then you have cell F11(Highlighted). That is where I would like any error message to appear for all of the G cells. So instead of having 3 errors in G, I want one cell for an error if the error happens in any of the listed G cells.

So the user enters scores in columns C & D and the sheet totals them in column E. The user SHOULD CHOOSE A REASON IN COLUMN F. If they do, no need for an error in Column G. If the scores total more than 0 and the user does not choose a reason an error should show. If there are no scores then the error cell should remain blank. I just do not want a whole range of errors in G. Just one Highlighted cell to look at. So, I have the formula needed for the individual cells. How do I apply it in the one cell (F11) to apply to rows 4 through 8, or cells f4 through f8 however you want to say it.

 

[Fluff]

if you see row 4 added, the score is >0

You were originally asking if col E wasn't 8, not <>0 so try

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2			Score1	Score2	Total Score	Reason
3
4		me	8	4	12	I won
5		you	6	6	12		no reason
6		he	1	0	1		no reason
7		her	-1	0	-1		no reason
8		they			0
9
10
11						Check
Terms

Cell Formulas
Range	Formula
E4:E8	E4	=SUM(C4:D4)
G4:G8	G4	=IF(AND(E4<>0,F4=""),"no reason","")
F11	F11	=IF(SUM((E5:E10<>"")*(E5:E10<>0)*(F5:F10=""))>=1,"Check","ok")

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2			Score1	Score2	Total Score	Reason
3
4		me	8	4	12	I won
5		you	6	6	12	a
6		he	1	0	1	a
7		her	-1	0	-1	a
8		they			0
9
10
11						ok
Terms

Cell Formulas
Range	Formula
E4:E8	E4	=SUM(C4:D4)
F11	F11	=IF(SUM((E5:E10<>"")*(E5:E10<>0)*(F5:F10=""))>=1,"Check","ok")

 

[rwe1187]

Before you ask, Yes I tried the F11 formula. So, I want to thank you for all your time, but the match with you and I just is not there. There is no simpler way that I can explain what I want. I had someone do this for me last year in 30 seconds, while i watched them type it online and unfortunately I lost the paper. I know what the formulas are I just do not know how to put in one cell and make it apply to multiple cells. This has nothing to do with the totals in column E being totaled. I just need to know how to put a formula in a cell, that is looking for text in a range of cells, based on the number in another and either show an error message if that text is missing and nothing if the text is there.

 

[Fluff]

But that is what the formula I suggested does. If one or more rows gives an error it is displayed in F11 as can be seen in the two mini sheets I posted.
If it is not doing what you want, then you need to explain what results you are getting that are wrong.

 

[Fluff]

You've obviously decided to ignore this

But if you do please ensure that you include any cross post links on both sites.

now also posted I need to calculate an answer for multiple cells in one cell