• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count horizontally

NARAYANK991

Excel Ninja
Hi ,

Can you explain a little more ?

And give more rows of data , and give the desired output in a few cases ?

Narayan
 

AlanSidman

Well-Known Member
Your data is not normalized and is more difficult to analyze in its current layout. I have changed the layout to a normalized status in the attached. Applied conditional formatting and a countif statement.
 

Attachments

HI Alan,

What you are doing is correct. How about horizontally?

There are some data which is not key in due to machine stop, therefore is blank.

Thanks.
 
Hi Ninja,

You are nearly there.

There is some clearer picture from the previous message as the above but not horizontal.

Using the horizontally 2.xlsx, could you use the conditional formatting on the pressure horizontally that is not higher or equal to 110 and change the text in red color.

Count how many pressure horizontally, e.g JAW302 has 3 pressure. The reason I need to count the pressure horizontally in order to inform the our technician to work on next pressure.

Hope that clear your understanding.

Thanks.
 

Attachments

Hi Ninja,

Another question that comes to my mind. How do you get the cell of $AA7:$AO7 from the countif?

Kindly advise with thanks.

64377
 
Hi Ninja,

I have other questions while I am progressing as below:-

The row data expands, on the additional car machine in the future, at the Name Manager as below image, could I create a new separate based on each row?

Cause, when I keep on adding to the continuous data at the "Refers To:" it might have some error later on.

Attached on the workbook.

Thanks.

64381
 

Attachments

Peter Bartholomew

Well-Known Member
Your data is not normalized and is more difficult to analyze in its current layout. I have changed the layout to a normalized status in the attached. Applied conditional formatting and a countif statement.
Modern dynamic arrays will in due course make it easier to deal with non-normalised data of this sort. In the present instance
= INDEX( data, SEQUENCE(3), SEQUENCE(1,5,2,3) )
would return a 3x5 array of pressures. Not that one is completely out of the woods at that point, since COUNTIFS does not accept array arguments and even summing rows requires MMULT (unless one accepts a helper range).
 

NARAYANK991

Excel Ninja
Hi ,

See the attached file.

1. A named range Tolerance has been defined and given a value of 1. You can reduce this to 0.5 or any other value. This has been used to colour cells yellow.

2. The named range Pressure has been extended till row 99. You can add more machine rows now.
 

Attachments

Hi Ninja,

A very much, thank you.

Questions:-

1) How to create a column in excel which has a 5 times continuous pressure, high - an example of the car machine - KL123? Refer image below.

2) How to create a column in excel which is not a 5 times continuous pressure, high but count again until to have 5 times continuous - an example of car machine JAC209, on 4/9/2019 and 8/9/2019 are 2 times continuous pressures high but not on 12/9/2019. Then, count again on the 15/9/2019 as it has a high pressure. Refer to image below:-

Let me know if it is still doubted at your side. We can accept any ninja excel ideas from you.

Thanks.

64397
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

For any new requirement , show in the uploaded worksheet , what the end results you want are. Otherwise , I am not able to understand your explanation.

Narayan
 
Hi Ninja,

Apologize on my tough explanation. Uploaded the worksheet.

Refer to the column name called status, which the high pressure is more than 110, will count as Yes-1, Yes-2 till Yes 5 continuously. If there is a no at the status column, that means it is not a continuous which need to recount the high pressure again. You can see the difference example results in the car machine KL123 and JAC209.

Let me know if you have doubt on my explanation.

Thanks.
 

Attachments

Hi Ninja,

Is there any solutions to my previous questions dated 9 Dec 2019?

Let me know your questions if there are any.

Thanks.
 
Hi Ninja,

Yes, we are nearly there and here are the questions.

I have uploaded the workbook with the highlighted in green (means completing the pressure) and red (means still alert for the technician to do the pressure) at the KL123 row.
The rest of the car machine rows are good.

How do you do it when the countif for the KL123 is still 1?
Reason: the highlighted in green is completed and highlighted in red still needs to be done.

Secondly, the formula =IF(OFFSET($C8, , 3 * (COLUMN(CC8) - COLUMN($CC8))) >= PressureThreshold, CB8 + 1, 0), why do we need to add another empty column - CB?
Could you do at the pressure column rather than a new column?

Third is what is the meaning of 3* (COLUMN(CC8) - COLUMN($CC8)))?

Hope that clarifies, let me know if you need more explanation.

Thanks.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

I do not understand why the first set of counts is highlighted in green , while the next set of counts is highlighted in red. Can you explain ?

What we are doing with the counts is to increment the previous count by 1 if the present reading is equal to or above the threshold ; since the first cell does not actually have a previous count , either we have a different formula for the first cell , or we use the cell to the left of it as the previous count ; we have to ensure that this cell does not have any data.

In the data , the data relating to pressure is in every 4th column ; when we are using helper columns , these are in consecutive columns ; thus to take the pressure reading from every 4th column , we use the multiplier of 3 ; this will give us column offsets of 0 , 3 , 6 , 9 , 12 ,... ; if you see , the pressure readings are in columns C , F , I , L , O ,... which correspond to the above offsets.

Narayan
 
Hi Ninja,

"I do not understand why the first set of counts is highlighted in green, while the next set of counts is highlighted in red. Can you explain?"

Explanation: For example, let say a technician see the car machine KL123 which have 5 consecutive times of pressure every week, that need to be settled, then green color has highlighted for 5 consecutive times after the date of 1/10/2019 completion with successfully. The next 5 consecutive times of pressure, if the technician have not completed the task, then highlighted it in red color such as to give them an alert. The countif will be 1 every time there is a 5 consecutive times, which is yet to complete.

"What we are doing with the counts is to increment the previous count by 1 if the present reading is equal to or above the threshold ; since the first cell does not actually have a previous count , either we have a different formula for the first cell , or we use the cell to the left of it as the previous count ; we have to ensure that this cell does not have any data."
Question : Still blur blur, is there any examples based from my workbook?

"In the data , the data relating to pressure is in every 4th column ; when we are using helper columns , these are in consecutive columns ; thus to take the pressure reading from every 4th column , we use the multiplier of 3 ; this will give us column offsets of 0 , 3 , 6 , 9 , 12 ,... ; if you see , the pressure readings are in columns C , F , I , L , O ,... which correspond to the above offsets."
Explanation: Great explanation, Ninja. I understand. :)

Let me know again, if you still need to further explain.

Thanks.
 

Attachments

Hi

Happy New Year.

I think manually change the color into green that I know it is completed and manually change red which is yet complete.

A new requirement on machine hour:-
If I want to do the machine hour, which is not more than 400, then should I do like the Pressure tab as a new tab?

Kindly advise.

Thanks.
 
Top