Excel Formula Help

[John C. Harris, MPA]

I hope I explain this well:

I have a spreadsheet set to calcualte Home Visit Reports for a Social
Service Agency

Column B is an entry field for a LEVEL
Colum D and E Have entry fields for number of visits.

Column F should look at Column E and determine the number. The current
formula is:
=IF(E26>0, E26/E26,0) which of course looks at E and if the number is
greater than 0 divides the number by itself and returns 100% as an answer,
if not it returns 0%. I also need the same formule to return a "No Entry" if
the field is Blank. I have tried adding : or IF(E26 is null, "No Entry") but
that doesn't work.

The next problem is Column G which if a string of IF statement that look at
the LEVEL in Column B and assigns a weight to the number. The current
formaul is:
=IF(C10="1-SS","3", IF(C10="2","1", IF(C10="3","0.5",
IF(C10="4","0.25","2"))))

This appeared to be working at first, but it will not return the correct
answer for any LEVEL excpet for 1-SS. All others are returned as 2. I assume
it has something to do with the "" "" but when removed the formula does not
work at all?

Any ideas on this anyone??

TIA

 

[M. lane]

I hope I explain this well:

I have a spreadsheet set to calcualte Home Visit Reports for a Social
Service Agency

Column B is an entry field for a LEVEL
Colum D and E Have entry fields for number of visits.

Column F should look at Column E and determine the number. The current
formula is:
=IF(E26>0, E26/E26,0) which of course looks at E and if the number is
greater than 0 divides the number by itself and returns 100% as an answer,
if not it returns 0%. I also need the same formule to return a "No Entry" if
the field is Blank. I have tried adding : or IF(E26 is null, "No Entry") but
that doesn't work.

Try this formula instead of the one above:

=IF(ISBLANK(E26),"NO ENTRY",IF(E26>0, E26/E26,0))

The next problem is Column G which if a string of IF statement that look at
the LEVEL in Column B and assigns a weight to the number. The current
formaul is:
=IF(C10="1-SS","3", IF(C10="2","1", IF(C10="3","0.5",
IF(C10="4","0.25","2"))))

You may want to think about doing data validation with a drop down list in
cell C10 if there are not many different possible entries, otherwise you run
the risk of having the user accidentally input slightly incorrect data and
therefore not get a correct answer out of excel. but try this:

=IF(C10="1-SS",3,IF(C10=2,1,IF(C10=3,0.5,(IF(C10=4,0.25,2)))))

Format cell C10 as general

 

[Ron Rosenfeld]

I hope I explain this well:

I have a spreadsheet set to calcualte Home Visit Reports for a Social
Service Agency

Column B is an entry field for a LEVEL
Colum D and E Have entry fields for number of visits.

Column F should look at Column E and determine the number. The current
formula is:
=IF(E26>0, E26/E26,0) which of course looks at E and if the number is
greater than 0 divides the number by itself and returns 100% as an answer,
if not it returns 0%. I also need the same formule to return a "No Entry" if
the field is Blank. I have tried adding : or IF(E26 is null, "No Entry") but
that doesn't work.

It seems as if E26 can be either a number 0..n or blank, or possibly a null
string. You want to return a 0 if there is a 0; a 1 if there is a number
greater than 0; and otherwise return No Entry.

One way of doing this, which avoids the problem of differentiating between
blanks and null strings, is the formula:

=IF(ISNUMBER(E23),--(E26>0),"No Entry")

The next problem is Column G which if a string of IF statement that look at
the LEVEL in Column B and assigns a weight to the number. The current
formaul is:
=IF(C10="1-SS","3", IF(C10="2","1", IF(C10="3","0.5",
IF(C10="4","0.25","2"))))

This appeared to be working at first, but it will not return the correct
answer for any LEVEL excpet for 1-SS. All others are returned as 2. I assume
it has something to do with the "" "" but when removed the formula does not
work at all?

Well you are comparing strings. When you enter 1-SS, since Excel cannot
interpret that as a number it turns it into a string. So your string
comparison to "1-SS" returns TRUE, and you get a "3".

I suspect that with your other entries, you are entering a number rather than a
string; in other words you are entering the number 2, rather than the string
"2". Since 2 does not equal "2", the comparison returns FALSE and the formula
returns your default of "2".

It is not clear to me why your LEVEL in column B needs to be a string. Nor is
it clear why you want a default of 2 if the LEVEL is not one of the four
possible entries you list.

If you remove all of the quotes, except for the one around "1-SS", your formula
should work as written. But be certain that what you want is what you have
expressed here.


--ron