Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2021, 02:22 AM
dare2join dare2join is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Novice
Formula to indicate RYG based on negative Value
 
Join Date: Oct 2021
Posts: 5
dare2join is on a distinguished road
Default Formula to indicate RYG based on negative Value


Views: 5 Size: 13.8 KB">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
File Type: xlsx Sample Excel.xlsx (12.5 KB, 5 views)
Reply With Quote
  #2  
Old 10-06-2021, 07:18 AM
p45cal's Avatar
p45cal p45cal is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
File Type: xlsx msofficeforums47759Sample Excel1.xlsx (17.3 KB, 8 views)
Reply With Quote
  #3  
Old 10-06-2021, 05:14 PM
dare2join dare2join is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Novice
Formula to indicate RYG based on negative Value
 
Join Date: Oct 2021
Posts: 5
dare2join is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 10-07-2021, 02:30 AM
p45cal's Avatar
p45cal p45cal is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #5  
Old 10-07-2021, 06:48 AM
ArviLaanemets ArviLaanemets is offline Formula to indicate RYG based on negative Value Windows 8 Formula to indicate RYG based on negative Value Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #6  
Old 10-07-2021, 09:03 PM
dare2join dare2join is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Novice
Formula to indicate RYG based on negative Value
 
Join Date: Oct 2021
Posts: 5
dare2join is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 10-07-2021, 09:04 PM
dare2join dare2join is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Novice
Formula to indicate RYG based on negative Value
 
Join Date: Oct 2021
Posts: 5
dare2join is on a distinguished road
Default

hi ArviLaanemets ,

I had propose a few type format to customers. but they don't really care and ask me to follow whats given......
Reply With Quote
  #8  
Old 10-08-2021, 03:28 AM
p45cal's Avatar
p45cal p45cal is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Please, again:
Quote:
Originally Posted by p45cal View Post
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.
Reply With Quote
  #9  
Old 10-13-2021, 04:54 PM
dare2join dare2join is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Novice
Formula to indicate RYG based on negative Value
 
Join Date: Oct 2021
Posts: 5
dare2join is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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
Reply With Quote
  #10  
Old 10-14-2021, 04:08 AM
p45cal's Avatar
p45cal p45cal is offline Formula to indicate RYG based on negative Value Windows 10 Formula to indicate RYG based on negative Value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft