Easy for anyone but me...

[elvmeister]

Hi, thanks for reading this and replying if you do.

I have a stock sheet at work that I use daily, I need it to 're-age stock' for month end allowing me to revalue it and put a monetary provision aside against a changing stock value.
My stock list doesn't take VAT in to account on vat qualifying stock but I have to revalue using the value plus vat. The lined boxes at end are mine. I am using the formula -
=IF(M3="y",K3,SUM(K3*1.2))
the sum will work once seems to be sensitive to caps on Y or N. all want is if says N the original figure I its says Y total siv line plus 20%.

No	Reg No	Description	Colour	Mileage	Date Reg	Pch Pr	Recon	SIV	Total SIV	Price	VAT	DIS	mth end		cap clean
39931			Eclipse Bl	53836	08/09/2010	6000	772.94	6000	6772.94	7000	n	144	159	6772.94	5725
65906			Blue	10119	27/03/2013	11225	212	9354.17	9566.17	10000	Y	119	134	11479.4	11250
65761			WHITE	8247	26/09/2013	12500	160.12	12500	12660.12	11000	N	116	131	15192.14	4975
66111			Black	15334	26/07/2012	4600	321.8	3833.33	4155.13	6000	Y	110	125	4986.156	4900
66221			Silver	11507	30/12/2011	4416.67	278.46	4416.67	4695.13	5890	Y	107	122	5634.156	27350
60490			Decuma Gre	4250	30/09/2013	11552.5	259.11	11552.5	11811.61	10408.33	Y	107	122	14173.93	14550
66550			Silver	14600	30/12/2011	5225	203.46	4354.17	4557.63	6000	Y	88	103	5469.156	5725
26087			Decuma Gre	52491	29/04/2011	29500	1044.97	29500	30544.97	29990	N	88	103	36653.96	13850
30308			Pure White	18964	03/09/2012	8000	586.77	8000	8586.77	9695	N	77	92	10304.12
40182			Island Blu	28292	19/11/2010	5000	525.72	5000	5525.72	6495	N	70	85	6630.864	19800
58886	BU63LCJ	Verso Icon 1.8 Multi	Tyrol Silv	2700	30/09/2013	13922.04	0	13922.04	13922.04	17790	Y	60	75

<colgroup><col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"><colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"><colgroup><col width="171" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6253;"><colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"><colgroup><col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"><colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" span="3"><colgroup><col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;"><colgroup><col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;"><colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="2"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><tbody>
</tbody>

 

[Scott Huish]

What column in your sample is your formula in.
My best guess is your table starts in B2 to match up with your formula.

No need for SUM, you are not summing anything, but this should be all you need:
=IF(M3="Y",K3,K3*1.2)

 

[elvmeister]

Hi thanks for that,well worked out off my poor example. That works , the only thing Icant do now is copy the formula down ,it stops working. However if I change the "Y" to lower case it picksthe sumu again. Also if I change the Y in the column that also makes it work. I think Ill be carrying on long hand lol.

 

[Scott Huish]

What exactly do you mean by "stops working"? Do you get an error what?

 

[elvmeister]

The formula doesn't work. It doesn't recognise the letter Y in column L, so the sum shows the original figure rather than the figure plus vat that I am trying to achieve. If I then overtype the letter in the sheet,its copied in, the sum works. Its as is the format of the information pasted in isn't recognised.

 

[Scott Huish]

I would then assume that there is something in the cell besides a capital Y, not that it isn't working.

I would suspect a space or a non-breaking space.

For one of these cells that isn't working, put a formula like this:
=LEN(M3)
but change the cell reference to whatever you need. What does it return?

What do the following return?

=LEN(TRIM(M3))

and

=SUBSTITUTE(M3,CHAR(160),"A")