IF D2 contains X then divide I2 by 10

[Bassmoore]

Hi All,

Have a major problem with our monthly call billing, we basically have a spreadsheet which shows a number called and the total cost of the call.

However our the calls for 0844 & 0845 numbers are 10x the actual cost!

Does anyone know how i can run a macro which will find the 0844 & 0845 numbers in the D column and then if it finds one, divides the total cost in column I by 10 on that particular row?


Thanks!

 

[SteveO59L]

what makes you believe you need vba?

=if(or(left(d1,4)="0844",left(d1,4)="0845"),i1/10,i1)

 

[Bassmoore]

what makes you believe you need vba?

=if(or(left(d1,4)="0844",left(d1,4)="0845"),i1/10,i1)

Thanks for your reply, how would i apply that to my spreadsheet? Sorry i don't have much experience with excel!

 

[SteveO59L]

populate a cell in an adjacent column (J?) with the formula, and fill down

 

[Bassmoore]

I would just like to be able to modify the whole sheet without having a different column, is this possible through a macro?

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[Bassmoore]

	Call Time	Destination	Called Number	Caller ID	Duration	Cost
1	Mar 3, 2015 09:47:50	UK Non Geographic Local (Local) - 0844	8444737377	846894	36:30:00	32.12

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

That is one row, i couldnt find how to attach the whole document, sorry!

 

[SteveO59L]

so in that example, because Called Number begins 844 (not 0844 - as in your original post) , you would like the cost of 32.12 to become 3.212 ?

 

[Special-K99]

1) in J1
=IF(OR(LEFT(IF(LEFT(F1,1)="0",F1,"0"&F1),4)="0844",LEFT(IF(LEFT(F1,1)="0",F1,"0"&F1),4)="0845"),I1/10,I1)
and copy down the column
2) Copy column J
3) Paste Values over the top of column I
4) Delete column J

You cant attach files on this forum.
You have to upload the file to an online storage site then place a link to it on this forum.

 

[Bassmoore]

Thanks for your reply, the only issue is, i have loads of other calls on here not just 0844 & 0845 numbers..

Heres the next line:

2

Mar 30, 2015 16:00:13

UK National (Local) - 01

1315532817

846894

15:50

0.16

<tbody>
</tbody>