• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Service Intervals

A

alexandros6600

New Member
Hello, i am trying to built up a machine expenses report that is based on service intervals.
The time span of the report extends on a 10 year plan and is divided in yearly quarters. Each machine requires a different service interval.
What i want to achieve is a formula that every time the service interval -expressed in months- is reached to mark it with a symbol -asterisk- at the corresponding cell.
For example Machine A needs to be serviced every 25,12 months. So an asterisk is marked at the corresponding quarter cell (q9).
At first this can be done with if and mod functions. But after the first criteria is met the remaining cells get filled with asterisks. I need a formula that marks an asterisk only at q9,q17,q25,q33 cells and the remaining should stay blank.

upload_2017-3-2_1-11-23.png


Thank you in advance
 

Attachments

  • Service Expenses.xlsx
    9.6 KB · Views: 6
Try,

In B3, formula copy down to B6

=IF(MID(A3,2,FIND("(",A3)-3)="1","",IF(MOD(MID(A3,2,FIND("(",A3)-3)-1,8)=0,"*",""))

Then, select B3:B6, Copy and Paste to D3, F3, H3, J3……

Regards
Bosco
 

Attachments

  • Service Expenses.xlsx
    11.4 KB · Views: 7
Thank you very much for your instant reply bosco_yip,
but i forgot to mention that the value 25.12 that corresponds to the service interval in months of the machine will be prompt at cell a1. So cell a1 serves as the reference cell in which every machine's service interval will be entered and the calculation of asterisks distribution will be initiated each time for a different machine.
In the new xlsx i've uploaded, i kept only the quarter numbers and highlighted the reference cell a1.

Thanks again

upload_2017-3-2_11-33-41.png
 

Attachments

  • Service Expenses-1.1.xlsx
    9.2 KB · Views: 6
Hello again,
Each machine should be serviced on a different date. The time required for the service is expressed in motnths. For example Machine A needs to be serviced every 25,12months. Machine B every 13,5 months. Machine C every 33,3 months. More machines will be added in the future. But the maximum service plan is 10 years (devided in quarters).
So when i enter the service interval value in cell a1 i.e for machine A
i would like the formula to mark an asterisk * in each cell, every time the service value is reached. So for machine A i would like an asterisk in cells F3,J3,N3,P4
For machine B (13,5 months) an asterisk should be placed in cells D3,F3,H4,J4,L5,N5,P6,R6 till the 10 year time is reached.
I've attached you an example for machine B.

Thanks a lot again
 

Attachments

  • Service Expenses-1.2.xlsx
    10 KB · Views: 5
1] If based on your calculation, on 13.5 months, an asterisk should be placed in cells D3,F3,H4,J4,L5,N5,P6,R6

Follow this logic, my calculation for 25.12 months and 33.3 months an asterisk should be :

2] For 25.12 months, placed in cells F3,J3,N4,R4

3] For 33.3 months, placed in cells F6,L5,R4

Please confirm whether [2] and [3] can meet with your required ?

Regards
 
You must log in or register to reply here.
Back
Top