In Need Of...

[ksagert]

Hey Everyone

I am trying to get a report for work, and I have a specific idea of what I need to do.

	A	B	C	D	E	F
1	SKU	Description	O/H	O/O	Price	Clearance
2	123456	Candy	10	5	19.94	If E2 Ends in a 4, F2=Y. If E2 Ends in anything else except for 4, then F2=N. In this case F2=N
3	987654	Pop	5	9	9.94	If E3 Price ends in a 4, F3=Y. If E3 ends in anything else except for 4, then F3=NIn this case, F3=Y

<tbody>
</tbody>

Does anyone know how to make this work for me??

 

[Weazel]

Ok, but why would E2 be "N" ?

You can try something like....in F2

=IF(--RIGHT(E2,1)<>4,"N","Y")

though I'm a little shaky on whether it will do what you need

 

[Rick Rothstein]

Ok, but why would E2 be "N" ?

You can try something like....in F2

=IF(--RIGHT(E2,1)<>4,"N","Y")

though I'm a little shaky on whether it will do what you need

I think your formula will incorrectly return "Y" for values ending in .40 (such as 123.40) or values ending in 4.00 (such as 54.00). This formula, on the other hand, appears to work correctly for all values...

=IF(MOD(100*E2,10)=4,"Y","N")

 

[Weazel]

I agree Rick, I am a little confused as to what the OP's final outcome needed to be.

"If E2 Ends in a 4, F2=Y. If E2 Ends in anything else except for 4, then F2=N. In this case F2=NO"

If you have a minute, could you explain what the MOD is doing? I'm not getting my head around it.

<tbody>
</tbody>

 

[ksagert]

What I need my final outcome to be, if the price ends with a 4 I need that cell to produce a Y, and if it ends in anything else I need it to produce a N

 

[Rick Rothstein]

What I need my final outcome to be, if the price ends with a 4 I need that cell to produce a Y, and if it ends in anything else I need it to produce a N

We know what you said, but your example said something different. This is from your original message...

Hey Everyone

I am trying to get a report for work, and I have a specific idea of what I need to do.

	A	B	C	D	E	F
1	SKU	Description	O/H	O/O	Price	Clearance
2	123456	Candy	10	5	19.94	If E2 Ends in a 4, F2=Y. If E2 Ends in anything else except for 4, then F2=N. In this case F2=N
3	987654	Pop	5	9	9.94	If E3 Price ends in a 4, F3=Y. If E3 ends in anything else except for 4, then F3=NIn this case, F3=Y

<TBODY>
</TBODY>

Does anyone know how to make this work for me??

Look at what I highlighted in red... our question is why is F2 supposed to equal N instead of Y?

By the way... did you try the formula I posted in Message #3... I think it does what you want.

 

[Aladin Akyurek]

What I need my final outcome to be, if the price ends with a 4 I need that cell to produce a Y, and if it ends in anything else I need it to produce a N

Are the prices always two-decimal figures?

And what are expected results for:

19.94

9.94

44

4.2

<tbody>
</tbody>

in D2:D6?

 

[ksagert]

Y
y
y
n

 

[Aladin Akyurek]

Y
y
y
n

What about

3.794 ?

Does a price with more two decimals exist?

 

[Rick Rothstein]

If you have a minute, could you explain what the MOD is doing? I'm not getting my head around it.

MOD returns the remainder after dividing the first argument by the second argument. Basically, it is (probably) how you first learned division. For example, consider this difvision...

11/4

when you first learned division, you did not calculate this to be 2.75, rather, you (probably) were taught the answer was 2 with a remainder of 3... the MOD function returns that remainder. So...

MOD(11,4) ==> 3

So then, what does MOD(number,10) do... it you work it out, you will find it returns the last number because that is the remainder after dividing a number by 10. Now, to get the last number from the OP's values into the correct position, we need to multiply it by 100 (because it is money) so as to "get rid of" the decimal point and make the "pennies position become the last value in a whole number (so we can divide it by 10 and return the "pennies value").