specific conditional formatting

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Specific conditional formatting

Specific conditional formatting

resolvedResolved · Medium Priority · Version 2003

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

Attached files...

if and or functions.xls

RE: specific conditional formatting

Thank you very much

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Using Excel's MODE function

Use Excel's MODE function to display the most common value present in a particular range of cells.

The Mode function looks like this:

=MODE(cell range)

As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.

View all Excel hints and tips


Server loaded in 0.08 secs.