Forum Discussion

RyanParr's avatar
RyanParr
Qrew Assistant Captain
9 years ago

Remaining Value Formula

I have a single table titled ?Equipment? that contains Status (Assigned, Returned), Term (36), Value, Start Date, End Date, and Remaining Value.

Each piece of Equipment deprecates it?s value every 36 months that it?s been used by a client.  So every calendar month that goes by, the equipment loses 1/36 of its value.

When a piece of equipment is sent to a client, the Start field date is filled out to reflect the first of whatever month it should be applied to and the status is set to ?Assigned?.

If a particular piece of equipment is returned to us, the Status field is set to ?Returned?.  It should no longer be deprecating any value.

 

I need help with a formula to come up Remaining Value field that takes into account the Status, any takers?

For bonus points also include formula help to come up with the End Date, based off of the "Start Date" and "Term"

  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    I think you would need a child table here to assign equipment to a client with a start, term, end date and assignment duration. This way you can track the total amount of time it has been used on many client assignments and not count the time it was not assigned to a client. the formula field would include a summary of the duration months on assignment across multiple assignments. Something like: ([summary field]/36)*[value]

    Let me know your thoughts.