If a result is 0 can a field be blank?

**Mediaid** · 03-23-2009, 07:25 AM

H2 formula is =OFFSET(A4,MATCH(MAX(D$4:D$1000),D$4:D$1000,0)-1,0)
I2 formula is =MAX(D4:D26)

If either of these result in a ZERO is it possible to leave the field blank?

I am extremely grateful for any assistance you can give.

Regards

Matthew Palmer

**DonkeyOte** · 03-23-2009, 07:34 AM

Avoid OFFSET as is Volatile - this:

=OFFSET(A4,MATCH(MAX(D$4:D$1000),D$4:D$1000,0)-1,0)

can be replaced with

=INDEX(A$4:A$1000,MATCH(MAX(D$4:D$1000),D$4:D$1000,0))

You don't specify what "Field" is... but perhaps you want:

=IF(AND(N(H2),N(I2)),"do whatever","")

**Mediaid** · 03-23-2009, 07:55 AM

Sorry, I wasn't very clear.

H2 asks which name appears most commonly in a range of fields. If no names appear, then it uses the first name alphabetically.

I2 looks for the highest number in a range, but if the highest number is zero, this is what it prints.

What I am trying to do is store customer feedback in a form, and try to get an average for each trainer (this works) and a table showing which trainer is best/worst in each area.

At the start of a month, it shows most trainers as having no ratings at 'Very Poor' and lists a trainer at the top.

so H2 would = John Doe I2 = 0

In the 'league' John Doe would be our worst trainer, although he has had no 'very poors'

Does that make sense?

Thank you for your help so far.

Regards

Matthew Palmer

EDIT: To be honest, it's just a cosmetic thing, I can live with it as it is, but any help, much appreciated.

**DonkeyOte** · 03-23-2009, 08:33 AM

I must confess I'm a tad confused

I don't understand the significance of the smaller range as used in I2 but if you want that to be blank rather than 0 for printing purposes then you could use a Custom Format on H2 & I2 of: [=0]"";General

**Cheeky Charlie** · 03-23-2009, 08:44 AM

You don't even need to do that, you can go to tools -> options -> view -> untick "zero values"

HTH

**DonkeyOte** · 03-23-2009, 08:46 AM

Yes, assuming of course there are no 0 values elsewhere that should be visible...

**Mediaid** · 03-23-2009, 08:50 AM

That has worked,

I must confess, I was trying to be too clever, and missed the more obvious answer!

Thank you for that.

I just tried to explain what I am, doing, but got myself confused. It now works perfectly though, so thank you

EDIT: There were some Zeros I wanted visible, but thanks to Charlie too.