Paul has attended:
Excel Intermediate course
Excel Advanced course
Specific conditional formatting
Hello,
I talked to Simon about this last Thursday but thought I'd put my specific question to the forum.
I work for a medical diagnostic scanning company and work with an invoice log. We have specific scanning fees based on type of scan and number of body areas. We also have a team of doctors who report on our scans and receive a fee for each scan reported.
In most cases the fee is a simple 20% of the total scanning fee (for MRI's, XRAY's etc). However, for PET and PET/CT scans we have to deduct £340 before calculating the 20%.
We have a column in our invoice logs for scanning fees but are having to work out the PET and PET/CT report fees separately as we haven't been able to come up with the correct conditional formatting formula which will automatically calculate the additional £340 deduction.
I know this is going to be an =IF calculation and the logical test is "PET" or "PET/CT" but after that I'm coming unstuck.
Can you come up with a formula for me?
Many many thanks!!!
Great course, by the way - I want to enrol for the advanced class some time soon.
RE: specific conditional formatting
Hello Paul
Thank you for your question, and we're glad to hear you found the course useful.
I've attached an example of how I think you could make this work (based on my understanding of your post). What I would suggest would be to have a separate column that works out the deduction if the scan description is a PET or PET/CT, then you can hide this column if you like but the total will still take account of the deductions. I haven't hidden the column in the attached example so that you can see the formula easily.
I hope this helps.
Kind regards
Amanda
if and or functions.xls
