If And complex formula help..please urgent

  • Thread starter Thread starter heather
  • Start date Start date
H

heather

Please help.
I am trying to create a formula in Excel that will allow me t
calculate the following:
if H2 = 5% then 0 for each 0.2% greater than 5% add 1%
I presume this will also give me a negative result if any 0.2% les
than 5% also
many thanks
Heathe
 
One way:

=(H2-5%)*5

If you don't want the negative numbers for <5%:

=MAX(0,(H2-5%)*5)
 
Heather,

How about

=(H2-5%)*5

It adds .5% for 5.1$ etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Yikes........I even made a mistake in my question.
it should read if H2 = 5% then 0 else for each 0.2% greater than 5% add
1 (not 1%)

Another question.
If H5 = 63 then 0 else for each 1 greater add 1

many thanks
 
Do you mean

=(H2-5%)*500

Second part

=H5-63

or

=MAX(H5-63,0)

if you don't want negatives



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top