# Cannot seem to get this formula to work properly

1. ## Cannot seem to get this formula to work properly

Hi There,

I am having trouble getting this formula to work properly. I think i have it right but i keep getting a false return and it is not giving me the response i need for multiple cells.

In the Example.

I need Column F to say what is in row C, only if what is in column A is populated and it

so something like this:

In column F, i need something like: If column C is less than 0 but Column A is populated return the value in Column C in F, but if Column C isles than 0 and column A is not populated return blank or 0, If column C is greater than zero and column A IS populated return value in Column C, but if Column C is greater than 0 and Column A IS NOT populated than return 0 or blank.

The problem is the subtotals are getting populated when i don't need them

Hopefully this makes sense, i have tried 100 ways from sunday but can't get both results in one formula,

2. ## Re: Cannot seem to get this formula to work properly

I think this is what you are after :

=IF(AND(C2>0, ISBLANK(A2)),0,C2)

It's the and function that is the secret you were missing.

3. ## Re: Cannot seem to get this formula to work properly

How would you deal with C2 if it was positive?? and still didn't want it to return the value in C2?? as i have positive subtotals too

4. ## Re: Cannot seem to get this formula to work properly

If I understand you correctly, these are your rules...

C<0, A >0 = C
C<0, A="" = 0
C>0, A>0 = C
C>0, A="" = 0

So try this, copied down...
=IF(A2=0,0,MAX(A2,C2))

5. ## Re: Cannot seem to get this formula to work properly

Are you just trying to do this?

EXAMPLE.xlsx

6. ## Re: Cannot seem to get this formula to work properly

Perhaps this might clear things up.

7. ## Re: Cannot seem to get this formula to work properly

Perhaps include your expected outcome, entered manually?

Also please confirm that these are the rules you need to work with?
C<0, A >0 = C
C<0, A="" = 0
C>0, A>0 = C
C>0, A="" = 0

8. ## Re: Cannot seem to get this formula to work properly

C<0, a >0 = c
c<0, a="" = 0
c>0, a>0 = a
c>0, a="" = 0

9. ## Re: Cannot seem to get this formula to work properly

On your new table, try this in F34, copied down. I used F so you could still see your answers 0 you can move it to E when you are satisfied

10. ## Re: Cannot seem to get this formula to work properly

Sorry, FDibbins, am i missing something?? did you forget to add something? sorry bit of a newb

11. ## Re: Cannot seem to get this formula to work properly

DUH sorry, I forgot to copy the formula, sorry...
=IF(A34="","",IF(C34<0,C34,A34))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1