#1




Formula to indicate RYG based on negative Value
Sample Excel1.xlsx
hi, please look at the sample excel. I would like to get the result from Col Y  Col AD with indication R Y G respectively. Col C  Col P are date by week. Col Q  Col V are date by month. as long as with row name at Col A is "Proj OH" that's the row value to indicate R Y G and it should tally the DATE between (Col Y  Col AD) with (Col C  Col S) G = there is totally NO negative value on Row Name "Proj OH" in between date Y = if there is 1 or 2 negative value on Row Name "Proj OH" between Col C  Col P R = if there is 3 or more negative value on Row Name "Proj OH" between Col C  Col P and negative value on Col Q  Col S. I have thousands of line every week, and I indicate them line by line. I hope I can have a formula for me to just copy paste down from Y2  AD2 until last row. thanks in advance whoever manage to help me with this. 
#2




In the attached, in columns AG:AJ are formulae. They can all be copied from cell AE5.
The results are in the same pattern as your manual entries but either I've made a mistake about what you want or your counting is awry (or your translation of counts to letters is). So for information only I've added the count part of the formula to columns AL:AQ (grey highlight) so you can check it's doing things right. After that you can delete columns AL:AQ. For this to work well, you need to have proper Excel dates in row 1: cells C1:V1 and cells AE1:AJ1. I have done this in the attached. The values you had in there were just strings. Excel might have interpreted them properly but you could never be certain of that. The values in your cells Y1:AD1 would not be recognised as dates at all. 
#3




hi, p45cal
appreciate your help. I have just tested the sample that u attached. I found out the result dint really match what I want because the result only G & Y. those R also turned to Y. do you mind to take a look again? G = there is totally NO negative value on Row Name "Proj OH" in between date Y = if 1 or 2 negative value on Row Name "Proj OH" between Col C  Col P R = if 3 or more negative value on Row Name "Proj OH" between Col C  Col P and negative value on Col Q  Col S. 
#4




It looks like I've misunderstood completely the requirement.
To help me better understand, I'd like you to explain to me how you arrived at the result in just one cell. Please refer to the file I attached in my last message so we're both talking about the same cells. I'm interested in how you arrive at the R in cell AB17, which is for row 17 and January 2022. You say R means there are 3 or more negative values. I'd like you to tell me: 1. Which 3 or more cells contain the negative values to get that R result in just that one cell AB17 2. Which cells should just that cell AB17 examine to see if they are negative or not. If you want to do the same with a few other result cells to make thing clearer, that's fine. Cell AD5 would be a good one. Last edited by p45cal; 10072021 at 06:13 AM. 
#5




Are different groups of rows (OnHand, ... , ProjOH) different projects?
You'd make your life much easier building your table(s) vertically! And then having a report sheet where user sets report conditions and gets result table. E.g. sheet "Registry" with columns like Week, Project, OnHand, SupplyQty, DemandQty, ProjOverhead. With such design, once you have all formulas in place, there is no need to edit them in future. You simply add new rows into table. And when the workbook slows down because of too many rows (think of tens or hundreds thousands rows  depends on amount and difficulty of formulas in report sheet(s)), you save a copy of old workbook as archived one, and then delete a bunch of older entries. 
#6




hi p45cal
from col C  P the date in weekly format, that's why need to check 45 col to determine the whole month. where col Q  V in monthly format which only 1 cell to determine either G or R only. 
#7




hi ArviLaanemets ,
I had propose a few type format to customers. but they don't really care and ask me to follow whats given...... 
#8




Please, again:

#9




Originally Posted by p45cal
I'd like you to tell me: 1. Which 3 or more cells contain the negative values to get that R result in just that one cell AB17 AB17 is month JAN, and cell Q1 is the only JAN to indicate with. its unable to compare 3 or not to indicate R. with one cell only, shld be either R or G 2. Which cells should just that cell AB17 examine to see if they are negative or not. as mentioned above AB17 is JAN, from Col C  Col V, only Col Q is JAN, so shld be either R or G 
#10




So when there's only one cell for a month/year combination it counts as 3.
See attached. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Need Conditional based percentage formula!  jocky6688  Excel  5  03152021 12:35 AM 
Formula to return a value of 1,2,3 or based on a set criteria  Highlander01  Excel  7  06022017 01:17 PM 
Need a formula to set a field based on two variables  unknownhere2000  Excel  2  01122016 01:05 PM 
Custom formatting to make negative numbers show w/o negative sign on charts  todor  PowerPoint  3  01162013 03:45 AM 
If formula result is negative then display text  oxicottin  Excel  3  12032012 02:12 AM 