Choosing formula

[Simmelimme]

Hello, 

I have this file at my workplace that calculates how much of a product I can fit on every pallet works super as long as we had this product earlier.. 

The trouble begins when its a new product...

I want the numbers in row I to be zero if there is a zero in row A, but all IFS and ORs formulas only gives me a false if its not a zero :(

A	B	C	D	E	F	G	H	I	J
Långsides		Loc	Loc	Ja	Nej		Ja	80	1
Långsides		Loc	Loc	Ja	Nej		Ja	164	2
0		S	Ej definierad	Ej definierad	Nej		Ja	84	504
0		S	Ej definierad	Ej definierad	Nej		Ja	600	600
Kortsides lång		M	M	Nej	Nej		Ja	350	9
Långsides		Loc	Loc	Ja	Nej		Ja	1400	26
0		M	M	Ej definierad	Nej		Ja	126	756
0		M	M	Ej definierad	Nej		Ja	120	720

Any tips for me would be greatly appreciated! :) 

[#2]

In cell I2, enter the formula
=IF(A2=0,0,A2)

Copy down the formula

[#3]

@Abiola1 

Sounds good, doesent work... :p 

Got the regular "There is something wrong with this formula" 

I attached a small test, tried both =IF(A2=0,0,A2) and =IF(A2=0,0,"")

Regards Simon

[#4]

@Simmelimme 

Hello, kindly see the solution as attached

[#5]

@Abiola1 

Yes this works with everone that got 0 in column A, problem is I want everything that dont have zero in column A untouched by the formula. 

So if it says in Column A Långsides I want the original numbers in Column L. 

Column L is a calculated number of products I can fit on a pallet, when there is a zero in column A we dont know how much product will fit on pallet so it gives a false number since I already have a looking for row to get the first number :p 

Regards Simon

[#6]

Hello, 

Anyone have any tips for me? :) 

[#7]

@Simmelimme 

Do you need to use "" around the zero to treat it as a character (which then distinguishes it from the number zero.

I cannot see formulae now, but something like =if(A2="0", 0, A2)

[#8]

@Simmelimme =IF(OR(A1=0,A1=""),0,L1)

[#9 · Accepted solution]

@Simmelimme

From what I see, you have 2 problems:

1. if in column A, the value is 0, how in I to be zero

2. if in column A, the value is not zero, how much should be then.

The simple, easy and consistent solution is to use 2 columns.

The first one, is to store how many items of this product fit on 1 pallet.

The second column is the formula. It checks if in column A, the text is 0, and if it is not - returns the value from the first column. From then on, in all following calculations, you should use the second column. 

The formula itself is: =IF(T(A3)="",0,I3)

Attached is and example file.

[#10]

@Simmelimme 

So, you want column I to be -zero- if A equals -zero-, but you want the calculated value from column L when A is not equal to -zero-. Then these formulae will achieve that. Make your choice.

=IF(A2=0,0,L2)

or

=(A2<>0)*L2