IF formula for 3 conditions in pivot table. (PIC)

[exceliskey]

Hi everyone,

I have a question for you experts. I have a pivot table that changes every month, and I need the cells directly beside it to automatically populate based on the values in the pivot table. For example:

\1

Top left cell is A1.

When the value is zero, I need the formula to have the adjacent cell automatically say "Paid"
When the value is any number other than zero, I need the adjacent cell to say "Outstanding"
When the cell is the name of the sport (Title of a pivot group), the adjacent cell should say N/A automatically

There are a lot of lines like this so having a formula can make the file much better.

Can you please help?

Thanks in advance.

[vlady]

Welcome to the forum.
Pictures are good but members want real workbook to play around.

[exceliskey]

Hi, thanks for the heads up.

So far this formula seems to be getting me on the right track, but I still need the headings of the groups to appear as N/A (C2, C5, C9 & C12 should be N/A).

Please Login or Register  to view this content.

Maybe an IfBold(B2,"N/A") or something is required in this situation? How would I include it in the formula I already am using?

[vlady]

maybe

=IF(MATCH(A2,$H$2:$H$12,0),IF(OR(B2>0,B2<0),"Outstanding","Paid"))

[exceliskey]

That formula works, but I need the n/a to be in text format rather than "#N/A" error format.

The cells in column C affect a formula in the final worksheet, so any %N/A error cells will cause prevent the other formulas from working

[vlady]

you can use the iferror since you have xl 2007

=IFERROR(IF(MATCH(A2,$H$2:$H$12,0),IF(OR(B2>0,B2<0),"Outstanding","Paid")),"N/A") ->bold is in text format N/A

[exceliskey]

you can use the iferror since you have xl 2007

=IFERROR(IF(MATCH(A2,$H$2:$H$12,0),IF(OR(B2>0,B2<0),"Outstanding","Paid")),"N/A") ->bold is in text format N/A

That's great, Ive thanked you for your help.

One more thing, how can I add to this formula so that if the value is between 0.01 to 1 OR -0.01 to -1, the message says "Progress". Is it even possible to do this?

If you can help me with this I would really appreciate it.

[vlady]

maybe i lost you can you create another condition this is my interpretation

if value is >= .01 and <=1 then progress.
if value is >1 then Outstanding
if value is 0 then Paid

is that right?

=IF(AND(MATCH(A2,$H$2:$H$12,0),AND(ABS(B2)>=0.01,ABS(B2)<=1)),"Progress",IF(B2>1,"Outstanding","Paid"))

you can then put the iferror statement if this is what you want.
=iferror(theformula,"N/A")