![]() |
IF formula to accept or reject based on numbers entered.
Im trying to set up a spreadsheet that will indicate whether or not multiple
values are acceptable or rejectable for percentages entered for a single line. For example.... Percentages entered into A3, B3, and C3 needs to be between 3.0 and 5.0..... if the percentage entered is 2.9 or below / 5.1 or above in any cell, it will automatically fill in E3 with an "X". And if any percentage entered is between 3.0 and 5.0 it will fill in D3 with an "X". Something like this. (as best as I could describe it) A B C D E 1 Element 1 Element2 Element3 2 3.0 - 5.0 3.0 - 5.0 3.0 - 5.0 A R 3 2.8 4.5 4.9 X 4 3.5 4.1 3.9 X I was able to accomplish this a long time ago, however it was several years ago and I dont have the first clue as to how I managed to accomplish it before. Can anyone please help????? |
IF formula to accept or reject based on numbers entered.
This can be done with IF formulas......
For example, this one will give you an "x" if any cell in the row is below 3, or if any cell in the row is above 5 =IF(MIN(A3:C3)<3,"x",IF(MAX(A3:C3)5,"x","")) What result would you like to see if you had mixed conditions in one row...ie, 2 4 6, or 4 4 6 or 2 4 4......... Vaya con Dios Chuck, CABGx3 "crabflinger" wrote: Im trying to set up a spreadsheet that will indicate whether or not multiple values are acceptable or rejectable for percentages entered for a single line. For example.... Percentages entered into A3, B3, and C3 needs to be between 3.0 and 5.0..... if the percentage entered is 2.9 or below / 5.1 or above in any cell, it will automatically fill in E3 with an "X". And if any percentage entered is between 3.0 and 5.0 it will fill in D3 with an "X". Something like this. (as best as I could describe it) A B C D E 1 Element 1 Element2 Element3 2 3.0 - 5.0 3.0 - 5.0 3.0 - 5.0 A R 3 2.8 4.5 4.9 X 4 3.5 4.1 3.9 X I was able to accomplish this a long time ago, however it was several years ago and I dont have the first clue as to how I managed to accomplish it before. Can anyone please help????? |
IF formula to accept or reject based on numbers entered.
I could really make this work if I were trying to get an answer of ACCEPT or
REJECT based on a percentage of anything outside of 3.0 to 5.0 being rejectable. The problem that I am having is that I have to consider multiple ranges and elements. Take for example that I have a piece of alloy steel. The specification of that particular alloy says it should have: CHROME 08.00 - 10.00 MOLYBDENUM 00.09 - 01.10 MANGANESE 00.30 - 00.60 Im trying to create a preset form so that when we perform a chemical analysis of the alloy, my inspectors can simply input the percentages they find. Based on the formulas I have entered, the form will automatically tell them whether or not their measurements are acceptable or rejectable. If every element reading falls within its own preset guidelines, the end result will indicate: ACCEPT. But if any one reading for that particular element falls outside of the ranges required, it will indicate: REJECT. I understand this seems difficult. I should know, Ive been trying to figure it out on my own for a little over a week, haha. But nonetheless, THANK YOU very much "CLR" wrote: This can be done with IF formulas...... For example, this one will give you an "x" if any cell in the row is below 3, or if any cell in the row is above 5 =IF(MIN(A3:C3)<3,"x",IF(MAX(A3:C3)5,"x","")) What result would you like to see if you had mixed conditions in one row...ie, 2 4 6, or 4 4 6 or 2 4 4......... Vaya con Dios Chuck, CABGx3 |
IF formula to accept or reject based on numbers entered.
Perhaps you could consider changing the way your form is setup then.
Using your alloy example, have in A1 "CHROME", C1 = "MOLYBDENUM" and E1= "MANGANESE" Below, in A2 and B2 have 8 and 10 respectively. C2 & D2 = .09 and 1.1 E2 & F2 = .3 and .6 For formatting purposes, hide columns B, D, and F. (or not, it's purely a visual thing, if you want these values displayed or not.) Now, your inspectors could input their findings into cells A3, C3, and E3. formula in your "Findings column" (which is cell G3) =IF(AND(A3=A$2,A3<=B$2,C3=C$2,C3<=D$2,E3=E$2,E3 <=F$2),"ACCEPT","REJECT") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "crabflinger" wrote: I could really make this work if I were trying to get an answer of ACCEPT or REJECT based on a percentage of anything outside of 3.0 to 5.0 being rejectable. The problem that I am having is that I have to consider multiple ranges and elements. Take for example that I have a piece of alloy steel. The specification of that particular alloy says it should have: CHROME 08.00 - 10.00 MOLYBDENUM 00.09 - 01.10 MANGANESE 00.30 - 00.60 Im trying to create a preset form so that when we perform a chemical analysis of the alloy, my inspectors can simply input the percentages they find. Based on the formulas I have entered, the form will automatically tell them whether or not their measurements are acceptable or rejectable. If every element reading falls within its own preset guidelines, the end result will indicate: ACCEPT. But if any one reading for that particular element falls outside of the ranges required, it will indicate: REJECT. I understand this seems difficult. I should know, Ive been trying to figure it out on my own for a little over a week, haha. But nonetheless, THANK YOU very much "CLR" wrote: This can be done with IF formulas...... For example, this one will give you an "x" if any cell in the row is below 3, or if any cell in the row is above 5 =IF(MIN(A3:C3)<3,"x",IF(MAX(A3:C3)5,"x","")) What result would you like to see if you had mixed conditions in one row...ie, 2 4 6, or 4 4 6 or 2 4 4......... Vaya con Dios Chuck, CABGx3 |
IF formula to accept or reject based on numbers entered.
|
IF formula to accept or reject based on numbers entered.
Try these formulas in these cells...
D3: =IF(SUMPRODUCT((A3:C3=3)*(A3:C3<=5))=3,"X","") E3: =IF(AND(A3<"",D3=""),"X","") and then copy them down. -- Rick (MVP - Excel) "crabflinger" wrote in message ... Im trying to set up a spreadsheet that will indicate whether or not multiple values are acceptable or rejectable for percentages entered for a single line. For example.... Percentages entered into A3, B3, and C3 needs to be between 3.0 and 5.0..... if the percentage entered is 2.9 or below / 5.1 or above in any cell, it will automatically fill in E3 with an "X". And if any percentage entered is between 3.0 and 5.0 it will fill in D3 with an "X". Something like this. (as best as I could describe it) A B C D E 1 Element 1 Element2 Element3 2 3.0 - 5.0 3.0 - 5.0 3.0 - 5.0 A R 3 2.8 4.5 4.9 X 4 3.5 4.1 3.9 X I was able to accomplish this a long time ago, however it was several years ago and I dont have the first clue as to how I managed to accomplish it before. Can anyone please help????? |
IF formula to accept or reject based on numbers entered.
Something like this (if it works)
<a href="http://s66.photobucket.com/albums/h270/space_pikey/?action=view¤t=WORKSHEETEXAMPLE.jpg" target="_blank"<img src="http://i66.photobucket.com/albums/h270/space_pikey/WORKSHEETEXAMPLE.jpg" border="0" alt="Photobucket"</a |
All times are GMT +1. The time now is 08:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com