Search for a new job

Find Jobs

 
Home > Careers & Employment > Technology > For Experts: IF function/SUMIF criteria question for Excel?

For Experts: IF function/SUMIF criteria question for Excel?

Category: Technology

th1a90th1a90


Two questions. First simple, second kind of complicated.

1. Is there any way I can use an "if function" or something else so that in case a calculation results in the "#VALUE" that it display a blank instead?

--------------------------------------¦

2. Say I have two columns of numbers (one in range A1:A100, the other in range B1:B100.

and I want the total % of A to B, which would be =SUM(A1:A100)/SUM(B1:B100) in %.

(The values in column B are reference prices for different procedures the hospital I work at performs, and the ones in column A are the values we are actually receiving for those procedures from an insurance companie.)

The problem is that I don't have all the values for A, but I do have all the values for B. So when I run the =SUM(A1:A100)/SUM(B1:B100) calculation, the result won't be accurate because some of the "A values" won't get summed, where as all the B values will.

So is there any way that I can use a SUMIF criteria in which the values in B would only get summed if there is a value adjacent to it in A? Therefore if I don't have a value in A5, then the value in B5 won't be used either in the formula above.

so would be something like this:

=SUM(A1:A100)/ SUMIF(B1:B100; criteria)

So, i'm trying to figure out how to write that criteria.

(I know that I could just delete the values in the B Cells that I don't have the value for its A equivilent,

but If I could somehow do it using the criteria method above, I would prefer to, since I don't want to remove values from the reference price list.)

GURU
GURU

I have some quick solutions for you...

Prob 1:

You can use the IF condition this way to display the blank cell by applying

=IF(ISERROR(B4)= TRUE,"","NO ERROR"). This will display a blank cell if B4 contains "#VALUE". Else the result will be "NO ERROR".

Prob 2:

You can apply an IF condition under column C like below. That will calculate the % only if cells of column A contains some value.

=IF(A2<>"",(A2/B2),"")

For ex:

ActualStandardCalculation

8100.80

11

10120.83

11130.85

14

14150.93

Hope this helps.

Let me know if you have any questions.

For the second one, you can try this one too...

=SUM(A1:A100)/SUMIF(A1:A100,">0",B1:B1¦


Share this on your favorite networks

Career Discussions

Megmarmalade Excel-test for interview. Please help!? (1 answers)

I have got a job interview next week and it includes a Word and an Excel test.Can anyone please let me know what to expect?I'm not great on Excel ...

DebraKay I am in high school and thinking about my future career and need some help,PLZ!? (1 answers)

I like helping other people and working with kids so a teacher would be a great job for me, but i need something that pays more and i worry that teach ...

Anthony Is This Good News or Bad News? (3 answers)

I was offered a job by Kmart on July 13th. The next day I took the drug screening and did paperwork. Still hadn't received a call back so this m ...

isa w I submitted an application for Target online, how can I make changes to my application once submitted? (1 answers)

I submitted my application and want to make changes to it. ...

ladyt I needed a job he offered one to, but it was a scam?(sorry its a long story)? (1 answers)

Well I'm a 20yr old college student, I was living with my mom until she passed a few weeks ago, so now I'm mourning,struggling in school,don ...

matilda.asks Advice on how to get a cocktail waitress job without experience? (3 answers)

Has anyone done this? I was a host for an upscale restaurant for about 5 months, but wasn't getting enough hours so had to quit. I'm tall, l ...

D.K Where can I get a part time job in Chennai with good communication and computer skills.? (1 answers)

With 8 hours to spend and 7 thousand salary to get. ...

C My Interests/Hobbies section of a CV. How do I make it look good with few interests? (1 answers)

I am struggling to fill in application forms and such with personal information for hobbies and such.My spare time is spent with friends drinking, par ...

vanessa n I made a mistake at work ? (3 answers)

Ok , so yesterday I was at work . Im 14 years old and I work at a little tutoring type institution . And so there were some pencil sharpenings on the ...

Odin Should I be worried about my potentially bad job references? (1 answers)

Hey I recently was offered a job in this god awful rotten job market. They will be checking references. Now 2/3 references I know will be good, howeve ...