If, Then Function

D

Dee

I have a spreadsheet that I want to track the visits of patients and the how
much I would owe the site for the visits. I have another spreadsheet that
works out the cost. per visit. For example, the patients would be seen
Screening, Day 1, Day 2, Day 8, Day 15, Day 22, Day 36 and Final Visit. The
cost for each visit varies. I have a spreadsheet that calculates the cost of
each visit and also the total cost depending on the number of visits. Say
patient 003 had completed Screening, Day 1 and Day 2, my second spreadsheet
calculates how much we would owe for the patient for the 3 visits. On my
first spreadsheet I have excel enter the dates of each visit by adding the
days to visit Day 1. At the end of the row I have a count of the number of
visits that have been completed. I would like to have excel enter the cost of
the number of visits. For example, if at the end of the row the patient had
completed 4 visits, I want excel to enter the cost of the 4 visits that has
been calculated on the second spreadsheet. There are 8 visits in all. What
type of formula could I use to achieve this. I want excel to look in say
cell F2, if there is a 4 in F2 how can I have excel enter the right cost for
4 visits in cell G2. Would I use an If, then function. If F2 is 4 then enter
$4,567.99 which is calculated on the second spreadsheet and if F2 is 5, then
enter $8,987.00 etc. I’m not sure how to approach this. Any help would be
greatly appreciated. I am using Excel 2003.

Best regards,

Dee
 
B

Bernard Liengme

What a long question? Have you read Help to see how VLOOKUP works - I think
that answers your question. Happy to give more info if question gets
refined.
best wishes
 
D

Dee

I know it was a bit long lets try this.

Sheet 1
Subject Day 1 Day 2 Day 3 Total # Visits Total
Owed
001 07/17/05 07/18/05 2
002 07/15/05 1

Sheet 2
# of Visits Cost

1 $1880.00
2 $3987.00
3 $4353.00
4 $5850.00 etc.

I would like excel to look in value of Total # visits in sheet 1 and match
it to # visits in sheet 2 and add the amount for the # of visits in sheet 1
in the total owed column.
Does this make sense?

Best regards,

Dee
 
R

Roger Govier

Hi Dee
You say in your original posts that there are up to 8 visits, so Iassume
your dates will be in columns B to H
In cell I2 of your Sheet1 enter
=COUNTIF(B2:H2"<>") and this will give the total number of visits
in cell J2 of Sheet1 enter
=VLOOKUP(I2,'Sheet 2'!$A$2:$B$9,2,0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top