Formula Help Please!

[Andrew_Wood]

Hi all,

I need a bit of help with a formula, I think I'm not really on the right track with this at all but not really sure what to do.

Basically - we hire products to people. We typically take the cost of the item "Pricing!I73" multiply by the quantity "H16" and then divide it by the hire period "D7". See below.

=MAX(10,(SUM(Pricing!I73*H16)/D7))

Basically I want to return a minimum value of £10 per item, regardless of the hire period (D7), the above formula works if the quantity (H16) = 1, but as soon as the quantity increases it obviously doesn't work!

Any help on what to do would be greatly appreciated!

[#2]

@Andrew_Wood  wrote:  ``want to return a minimum value of £10 per item``

Formula looks okay for that purpose.  The syntax can be simplified as follows; but what you wrote is not wrong.  Better:  =MAX(10, Pricing!I73*H16/D7)

Explain what you mean by ``obviously doesn't work``.  What is expected for what values in I73, H16 and D7?  What is returned instead?

Perhaps you need the following, in case D7 is zero (or empty):

=IFERROR(MAX(10, Pricing!I73*H16/D7), "")

But that has nothing to do with increasing H16 beyond 1.

Attach an example Excel file.  Click "browse" near the bottom of the reply window.

If the forum does not allow you to click "browse", upload the file to a file-sharing website, and provide the download URL (*).  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it uses the same login as this forum

(*) If the forum does not allow you to enter a URL, spell out the first part; for example, techcommunity dot microsoft dot com /t5/excel/formula-help-please/m-p/3576484