Round UP for Cut Materials

[vini.v4]

Hi folks,


I need to a help to round up or down (depend on the number) all the numbers different than 150,200,250,300,450, 600 on the collums E and F
Also, use these new values on the formula =COUNTIF(E4:E11,"300") to show how many of these numbers I have on E8.
per example: on E10 i have 340 and I`d like to change this number to 450, only to use 450 to count on I9,
the problem is that I dont wanna show the round up value on E10, i wanna keep same.

to clarify we have this na, pa sizes standarts 150,200,250,300,450,600. When we have an order that require us to cut these material we do but we need to deduct from the standart size.




thanks in advance

[AliGW]

Welcome to the forum.

There are insructions in the yellow banner at the top of the page telling you how to attach your workbook.

[vini.v4]

attachment fixed

[Gregb11]

I'm unsure on how you want to round, but what I'm assuming is that, say, for PA, you want to count for 150, those that are >= 150 and less than 200, etc.

So in I5, you could use this formula and then copy it down:
=IF(H6="",COUNTIFS($E$4:$E$11,">="&H5),COUNTIFS($E$4:$E$11,">="&H5,$E$4:$E$11,"<"&H6))

In L5,
=IF(K6="",COUNTIFS($F$4:$F$11,">="&K5),COUNTIFS($F$4:$F$11,">="&K5,$F$4:$F$11,"<"&K6))

If you meant to round in a different way, please let us know.

[menem]

Please try
I5

Please Login or Register  to view this content.

L5

Please Login or Register  to view this content.

But I doubt what is *4 in your formula ? >_<

Regards.

[vini.v4]

hey gregb11 and menem, thank you so much for helping

can you explain to me the logic on this `if`,
=IF(H6="",COUNTIFS($E$4:$E$11,">="&H5),COUNTIFS($E$4:$E$11,">="&H5,$E$4:$E$11,"<"&H6))
h6 will never be null

-
not only 150 need to round up
also
I cut from 600 NA(need to count as 600 any number on the following criteria):
<100 and >150
<450 and >600

from 450 NA(need to count as 450 any number on the following criteria):
<300 and >450
<250 and >300
<200 and >250
<150 and >200
-
To produce one item I use 4 NA and 4 PA, that's why I multiply 4 times the number of the qty
per exemple:
qty:10 size:150x150
NA: 40
PA: 40


=IF(H6="",COUNTIFS($E$4:$E$11,">="&H5)*4*QTYCOLUM`D`,COUNTIFS($E$4:$E$11,">="&H5,$E$4:$E$11,"<"&H6)*4*QTYCOLUM`D`)

[menem]

Greb11's formula is tested on next row.
The above formula is in 5th row but test for 6th row , for checking is 5th is the last row of table.

While my formula use another method, I use Current level - Prev.Level .

Regards.

I might explain something not clear , due to my English. >_<

[Gregb11]

As menem states, I'm checking to see if the next row is empty. This allows you to create one formula and copy it down (it's used in I10).

I don't understand this:
not only 150 need to round up
also
I cut from 600 NA(need to count as 600 any number on the following criteria):
<100 and >150
<450 and >600

When I read this, to me it says, "Round these numbers to 150 - those that are less than 100 and greater than 150. Well, NO number is less than 100 AND greater than 150. Maybe you mean count those numbers that are greater than 100 and less than 150? And also greater than 450 and less than 600?

Also, you say that:
from 450 NA(need to count as 450 any number on the following criteria):
<300 and >450
<250 and >300
<200 and >250
<150 and >200

Again, if I take it as opposite of what I see, do you mean any number greater than 300 and less than 450?
Greater than 250 and less than 300? etc. If so, is that the same thing as saying Greater than 150 and less than 450?

[vini.v4]

sorry i meant
600NA:
100 to 150
450 to 600

450NA:
300 to 450
250 to 300
200 to 250
150 to 200

"Again, if I take it as the opposite of what I see, do you mean any number greater than 300 and less than 450?
Greater than 250 and less than 300? etc. If so, is that the same thing as saying Greater than 150 and less than 450?"

it's not the same because when we have an order using the exact size of standard sizes (100,150,200,250,300,450,600)
we don't need cutting.

in case of any size between these standards we have to cut
p ex:

330x330 isn't standard size and it is between 450mm and 300mm we will cut from 450mm NA
300x300 don't need any cutting is standard size
270x270 isn't standard size and it is between 250mm and 300mm we will cut from 450mm NA
we don't cut from the greater standard size (300 would be) because 150,200,300 has holes and any cut wouldn't centralize them.

[huuthang_bd]

Try

Please Login or Register  to view this content.

Fill down I5:I8

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Same formulas for NA size.

However, it seems wrong to use 2 materials of size 600 to have sizes 120 and 480.

[menem]

If you don't mind to use helper table.
Please test result in this file.

By create helper table H3:O16

The formula in I5 will be

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for ARRAY formula.

and formula in L5 will be

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for ARRAY formula.


Regards.

[vini.v4]

hey huuthang_bd,gregb11,menem

most apretiate your help. I partialy found what I was looking for using these formulas below


L5 - =(COUNTIF($F$4:$F$11,"="&K5)-SUM(L$4:L4))*4

L6 - =(COUNTIF($F$4:$F$11,"="&K6)-SUM(L$4:L4))*4

L7 - =(COUNTIF($F$4:$F$11,"="&K7)-SUM(L$4:L4))*4

L8 - =(COUNTIF($F$4:$F$11,"="&K8)-SUM(L$4:L4))*4

L9 - =(COUNTIF($F$4:$F$11,"="&K9)-SUM(L$4:L4))*4

L10 - =((COUNTIF($F$4:$F$11,"="&K10)+COUNTIFS($F$4:$F$11,">"&K6,$F$4:$F$11,"<"&K7)+COUNTIFS($F$4:$F$11,">"&K7,$F$4:$F$11,"<"&K8)+COUNTIFS($F$4:$F$11,">"&K8,$F$4:$F$11,"<"&K9)+COUNTIFS($F$4:$F$11,">"&K9,$F$4:$F$11,"<"&K10))*4)

L11 - =((COUNTIF($F$4:$F$11,"="&K11)+COUNTIFS($F$4:$F$11,">"&K5,$F$4:$F$11,"<"&K6)+COUNTIFS($F$4:$F$11,">"&K10,$F$4:$F$11,"<"&K11))*4)


with the result returned with these formulas I need multiplicate with the quantity on the colum D

[menem]

Due to use COUNTIF in your formula, I cannot bring D's value to multiply with each elements (Others may can do that >_<" ).
But if in my formula it can add a multiple for each element.

Please check & verify result in this file.

Regards.

[vini.v4]

Legend!!!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.