Microsoft Office Forums Formula to indicate RYG based on negative Value
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-06-2021, 02:22 AM
 dare2join Windows 10 Office 2019 Novice Join Date: Oct 2021 Posts: 5
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.
Attached Files
 Sample Excel.xlsx (12.5 KB, 1 views)
#2
10-06-2021, 07:18 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 537

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.
Attached Files
 msofficeforums47759Sample Excel1.xlsx (17.3 KB, 4 views)
#3
10-06-2021, 05:14 PM
 dare2join Windows 10 Office 2019 Novice Join Date: Oct 2021 Posts: 5

hi, p45cal

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
10-07-2021, 02:30 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 537

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; 10-07-2021 at 06:13 AM.
#5
10-07-2021, 06:48 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 656

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
10-07-2021, 09:03 PM
 dare2join Windows 10 Office 2019 Novice Join Date: Oct 2021 Posts: 5

hi p45cal

from col C - P the date in weekly format, that's why need to check 4-5 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
10-07-2021, 09:04 PM
 dare2join Windows 10 Office 2019 Novice Join Date: Oct 2021 Posts: 5

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
10-08-2021, 03:28 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 537

Quote:
 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 2. Which cells should just that cell AB17 examine to see if they are negative or not.
#9
10-13-2021, 04:54 PM
 dare2join Windows 10 Office 2019 Novice Join Date: Oct 2021 Posts: 5

Quote:
 Originally Posted by p45cal Please, again:
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
10-14-2021, 04:08 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 537

So when there's only one cell for a month/year combination it counts as 3.
See attached.
Attached Files
 msofficeforums47759Sample Excel1.xlsx (16.3 KB, 1 views)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post jocky6688 Excel 5 03-15-2021 12:35 AM Highlander01 Excel 7 06-02-2017 01:17 PM unknownhere2000 Excel 2 01-12-2016 01:05 PM todor PowerPoint 3 01-16-2013 03:45 AM oxicottin Excel 3 12-03-2012 02:12 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:28 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top