hello all,
I am open to suggestion on how to build the pivot table i am after OR just the formula to return the value i need to then try and build the pivot table. Thank you in advance!!
I am trying to create a pivot table template that will return a view by name if any area in certain columns are 'under' or 'over' certain range- (say .8 and under or 1.1 and over). Preferably with a filter option for that under/over view. The source data does not contain this information identified, so i am adding a new column with a formula to return the under/over value and then have the pivot grab it. Problem is my formula is not working as desired. Results are mixed if any of the columns have zero or blank values. logically i understand if say raw 1 has 10 columns to view for that under/over value, how would the formula know which value to return for that row if 1 column has a 0 value, another column is blank, another column has a .7 value and another column has a 1.2 value. This means that row has all conditions. I am interested on is which raw is under or over and quickly direct my eyes/identify to the column that is under or over.
After trying several IF formulas with OR and AND logics, i realize i dont know how to solve for the above logic (if a raw contains different columns satisfying all conditions [1 column is over, another column is under] how does the formula know what to return back as an answer?)
IF formula to return values depending on 3 conditions across multiple columns. What i am after is for a cell to populate "Under" or "Over" or "none" if any of the 11 columns contains value 0.8 or less, or 1.1 or more. Bellow is the desire return in the "under/over" column:
I tried: (but results do not give the 'none' for the first row) =IF(OR(AND(V2>0,V2<0.8),AND(W2>0,W2<0.8),AND(X2>0,X2<0.8),AND(Y2>0,Y2<0.8),AND(Z2>0,Z2<0.8),AND(AA2>0,AA2<0.8),AND(AB2>0,AB2<0.8),AND(AC2>0,AC2<0.8),AND(AD2>0,AD2<0.8),AND(AE2>0,AE2<0.8),AND(AF2>0,AF2<0.8),AND(AG2>0,AG2<0.8),AND(AH2>0,AH2<0.8),AND(AI2>0,AI2<0.8)),"Under",IF(OR(V2>1.1,W2>1.1,X2>1.1,Y2>1.1,Z2>1.1,AA2>1.1,AB2>0.8,AC2>1.1,AD2>1.1,AE2>1.1,AF2>1.1,AG2>1.1,AH2>1.1,AI2>1.1,),"Over","None"))
I also tried: (but results only returns 'under' for all cells)
=IF(OR(D15<0.8,E15<0.8,F15<0.8,G15<0.8,H15<0.8,I15<0.8,J15<0.8,K15<0.8,L15<0.8,M15<0.8,N15<0.8,O15<0.8,P15<0.8),"Under", IF(OR(D15>1.1,E15>1.1,F15>1.1,G15>1.1,H15>1.1,I15>1.1,J15>1.1,K15>1.1,L15>1.1,M15>1.1,N15>1.1,O15>1.1,P15>1.1,),"Over","None"))
Maybe a solution would be to build the pivot such as to have a filter criteria for each of the columns in question, then a filter criteria if that column is over or under; but then what if we we want to view 4 columns at a time to see when either one of the columns is under or over (mind that there are 1,000+ rows)
sample of the pivot i am after:
I am open to suggestion on how to build the pivot table i am after OR just the formula to return the value i need to then try and build the pivot table. Thank you in advance!!
I am trying to create a pivot table template that will return a view by name if any area in certain columns are 'under' or 'over' certain range- (say .8 and under or 1.1 and over). Preferably with a filter option for that under/over view. The source data does not contain this information identified, so i am adding a new column with a formula to return the under/over value and then have the pivot grab it. Problem is my formula is not working as desired. Results are mixed if any of the columns have zero or blank values. logically i understand if say raw 1 has 10 columns to view for that under/over value, how would the formula know which value to return for that row if 1 column has a 0 value, another column is blank, another column has a .7 value and another column has a 1.2 value. This means that row has all conditions. I am interested on is which raw is under or over and quickly direct my eyes/identify to the column that is under or over.
After trying several IF formulas with OR and AND logics, i realize i dont know how to solve for the above logic (if a raw contains different columns satisfying all conditions [1 column is over, another column is under] how does the formula know what to return back as an answer?)
IF formula to return values depending on 3 conditions across multiple columns. What i am after is for a cell to populate "Under" or "Over" or "none" if any of the 11 columns contains value 0.8 or less, or 1.1 or more. Bellow is the desire return in the "under/over" column:
| under/over | Name | 6/19/2022 (f) | 6/26/2022 (f) | 7/3/2022 (f) | 7/10/2022 (f) | 7/17/2022 (f) | 7/24/2022 (f) | 7/31/2022 (f) | 8/7/2022 (f) | 8/14/2022 (f) | 8/21/2022 (f) | 8/28/2022 (f) |
| none | test1 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| under | test2 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 |
| over | test3 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 |
| under | test4 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 |
| what should the formula return? | test5 | 2 | .7 | 1 |
I tried: (but results do not give the 'none' for the first row) =IF(OR(AND(V2>0,V2<0.8),AND(W2>0,W2<0.8),AND(X2>0,X2<0.8),AND(Y2>0,Y2<0.8),AND(Z2>0,Z2<0.8),AND(AA2>0,AA2<0.8),AND(AB2>0,AB2<0.8),AND(AC2>0,AC2<0.8),AND(AD2>0,AD2<0.8),AND(AE2>0,AE2<0.8),AND(AF2>0,AF2<0.8),AND(AG2>0,AG2<0.8),AND(AH2>0,AH2<0.8),AND(AI2>0,AI2<0.8)),"Under",IF(OR(V2>1.1,W2>1.1,X2>1.1,Y2>1.1,Z2>1.1,AA2>1.1,AB2>0.8,AC2>1.1,AD2>1.1,AE2>1.1,AF2>1.1,AG2>1.1,AH2>1.1,AI2>1.1,),"Over","None"))
I also tried: (but results only returns 'under' for all cells)
=IF(OR(D15<0.8,E15<0.8,F15<0.8,G15<0.8,H15<0.8,I15<0.8,J15<0.8,K15<0.8,L15<0.8,M15<0.8,N15<0.8,O15<0.8,P15<0.8),"Under", IF(OR(D15>1.1,E15>1.1,F15>1.1,G15>1.1,H15>1.1,I15>1.1,J15>1.1,K15>1.1,L15>1.1,M15>1.1,N15>1.1,O15>1.1,P15>1.1,),"Over","None"))
Maybe a solution would be to build the pivot such as to have a filter criteria for each of the columns in question, then a filter criteria if that column is over or under; but then what if we we want to view 4 columns at a time to see when either one of the columns is under or over (mind that there are 1,000+ rows)
| Book1 | ||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | ||||||||||||||||||||||
| 1 | under/over | Name | 6/19/2022 (f) | 6/26/2022 (f) | 7/3/2022 (f) | 7/10/2022 (f) | 7/17/2022 (f) | 7/24/2022 (f) | 7/31/2022 (f) | 8/7/2022 (f) | 8/14/2022 (f) | 8/21/2022 (f) | 8/28/2022 (f) | |||||||||||||||||||||
| 2 | Under | test1 | 1.00 | 2.00 | 2.00 | .00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | ||||||||||||||||||||||
| 3 | Under | test2 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | .76 | |||||||||||||||||||||
| 4 | Under | test3 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | 1.20 | ||||||||||||||||||||||
| 5 | Under | test4 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | .00 | |||||||||||||||||||||
| 6 | Under | test5 | 1.00 | 1.00 | 2.00 | |||||||||||||||||||||||||||||
Sheet3 | ||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A6 | A2 | =IF(OR(V2<0.8,W2<0.8,X2<0.8,Y2<0.8,Z2<0.8,AA2<0.8,AB2<0.8,AC2<0.8,AD2<0.8,AE2<0.8,AF2<0.8),"Under", IF(OR(V2>1.1,W2>1.1,X2>1.1,Y2>1.1,Z2>1.1,AA2>1.1,AB2>1.1,AC2>1.1,AD2>1.1,AE2>1.1,AF2>1.1,),"Over","None")) |
sample of the pivot i am after:
| Resource Management and Assignments - BPPC ALL 6-27-3.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 2 | ||||||||||||||
| 3 | Under/Over | (All) | ||||||||||||
| 4 | ||||||||||||||
| 5 | Over/Under | Row Labels | Sum of 6/26/2022 (f) | Sum of 7/3/2022 (f) | Sum of 7/10/2022 (f) | Sum of 7/17/2022 (f) | Sum of 7/24/2022 (f) | Sum of 7/31/2022 (f) | Sum of 8/7/2022 (f) | Sum of 8/14/2022 (f) | Sum of 8/21/2022 (f) | Sum of 8/28/2022 (f) | ||
| 6 | Under Utilized | test 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
| 7 | Under Utilized | test 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
| 8 | Under Utilized | test 3 | 0.94 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | ||
| 9 | Under Utilized | test 4 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
| 10 | Under Utilized | test 5 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | 1.06 | ||
Pivot Main | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A6:A10 | A6 | =IF(OR(C5,0.8,D5<0.8,E5<0.8,F5<0.8,G5<0.8,H5<0.8,I5<0.8,J5<0.8,K5<0.8,L5<0.8,),"Under Utilized", IF(OR(C5>1.1,D5>1.1,E5>1.1,F5>1.1,G5>1.1,H5>1.1,I5>1.1,J5>1.1,K5>1.1,L5>1.1,,),"Over Utilized","None")) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C6:L6,C7:L7,C8:L8,C9:L9,C10:L10,C11:L11,C12:L12,C13:L13,C14:L14,C15:L15,C16:L16,C17:L17,C18:L18,C19:L19,C20:L20,C21:L21,C22:L22,C23:L23,C24:L24,C25:L25,C26:L26,C27:L27,C28:L28,C29:L29,C30:L30,C31:L31,C32:L32,C33:L33,C34:L34,C35:L35,C36:L36,C37:L37,C38:L38 | Cell Value | between 0 and 0.8 | text | NO |
| C6:L6,C7:L7,C8:L8,C9:L9,C10:L10,C11:L11,C12:L12,C13:L13,C14:L14,C15:L15,C16:L16,C17:L17,C18:L18,C19:L19,C20:L20,C21:L21,C22:L22,C23:L23,C24:L24,C25:L25,C26:L26,C27:L27,C28:L28,C29:L29,C30:L30,C31:L31,C32:L32,C33:L33,C34:L34,C35:L35,C36:L36,C37:L37,C38:L38 | Cell Value | >1.1 | text | NO |
