I need to create a list of my clients at the home where I work, I need to enter their weights monthly, in kilos and stones, I also want it to show percentages of weight increase/weight loss over varying durations: ie weight loss % over 6 months or 3 months. and an indicator to show dangerous weight loss: ie extreme weight loss over a 3 month period would show up as red for example.
I know you cant tell me how to do all this as ive never really used excel but could someone please point me in the right direction as to what type of chart etc to use or maybe find a youtube clip explaining this. I have looked on youtube but not sure what im looking for.
Initial thoughts are setup a summery sheet as worksheet one and then an individual worksheet for each client as I think there is going to be too much info to fit all clients onto one sheet together.
Working out the percentage is easy, just divide one result by the other and set the cell formatting to be percentage, you can then also use conditional formatting to change the cell colour to red if it's less than a set percentage.
I was thinking about suggesting things but didn't as I am not an expert. It is the calculation over time that is my problem. I keep getting VLOOKUP, HLOOKUP functions involved and haven't used them for some time.
Unless I have misunderstood it sounds simple enough.
Write the client name in the first cell. Use the cells to the right of that for the monthly kilo readings. Use the cell under each kilo reading to multiple the kilo reading by a factor to turn it into stones. Use the cell under each stones reading to show the loss from the previous column weight. Then under that the % 3 month loss, under than the % 6 month loss. Use conditional formatting to highlight any excessive loss.
Leave a row space and then start on the next client. Or modify the above to suit your needs.
Charts would be on a separate worksheet and is left as an exercise for the reader.
Column A) Clients b= Kilo c= Stones (use equation to work out kilo to stones) d= Month one + or - format cell to show - figure in red e= month 2 etc etc
We have 31 clients, creating worksheets for each client would not be a problem, I just need to keep it as simple as possible, like I said Ive really never used Excel and I think this may be something I could cut my teeth on.
I'm looking int your answers at the moment, looking interesting :-)