Problem with an auctioneer's spreadsheet!

[jckincaid]

Hi everyone

I'm having trouble trying to tweak a spreadsheet for an auctioneer. He has a minimum buyer premium (15%) of £1 so we need a column that shows the minimum of £1 and I've done this with a MAX formula HOWEVER it always shows £1 even if I haven't input a hammer price. I'm assuming that I have to use a couple of hidden column to get to where I want to be but here is my query:-

How can I have a cell show zero if no hammer price is input BUT show the contents of another cell (where the MAX calc is done - Column L) if a value is input.

Hammer price is input in Column F
Buyer Commission is in hidden Col K (=F*0.15)
Min of £1 is shown in hidden Col L (=MAX(1,K...)

I hope that's not too convoluted!!

Jon

[Fotis1991]

Hi

Jon. Can you upload a small sample workbook?

Looks to me that maybe an if in your max formula, will do the job.

IF(F1,"","","YOUR max FORMULA"....

[jckincaid]

sample.xlsx

I think I've attached it now! thanks for your help

[Fotis1991]

...How can I have a cell show zero if no hammer price is input BUT show the contents of another cell (where the MAX calc is done - Column L) if a value is input.

In which column do you want to do this?

[jckincaid]

Hi

column M has to show either 0 if nothing in Column F or the contents of the appropriate cell in column L if otherwise

[Fotis1991]

Do you mean?

=IF(F2="","",MAX(1,K2))

In M2 and copy down..

[jckincaid]

That's fantastic, thanks.

It works, now I need to work out for myself what it all means!!

Thanks again

Jon

[Fotis1991]

You are welcome

=IF(F2="","",MAX(1,K2))

This means that if F2, is empty(""), then leave cell M2, empty(""), or if it's not empty, then let formula(MAX(1,K2), works...

Pls, mark your THread, as Solved.