Not more than 100%

[PunkDaddy]

Hello,
Looking for a way to prevent a formula to have an answer with more than 100%. I tried using data validation but it seem to allow it

A1 = 2
A2 = 1
A3 = Formula A1/A2

Looking for way to kick an error out when the numerator is larger than the denominator .

Thanks!

[TMS]

=IF(A1>A2,1,A1/A2)


Regards, TMS

[RHCPgergo]

Hi,

how about something like this:
=IF(A1>A2,"over 100%",A1/A2)

edit: ok, looks like I misunderstood.

[Pepe Le Mokko]

Also =MIN(A1/A2,1)

[PunkDaddy]

Will it make any difference if I am doing this within a table

for example

=IF(Table4[[#This Row],['# of 1''s]]>Table4[[#This Row],[Amt. Audit]],over 100%,Table4[[#This Row],['# of 1''s]]/Table4[[#This Row],[Amt. Audit]])

Or

=IF(Table4[[#This Row],['# of 1''s]]>Table4[[#This Row],[Amt. Audit]],1,Table4[[#This Row],['# of 1''s]]/Table4[[#This Row],[Amt. Audit]])

[TMS]

No, I don't think so, but the "over 100%" needs to be in quotes.


Regards, TMS

[PunkDaddy]

It is kicking an error in the formula each time.

=IF(Table4[[#This Row],['# of 1''s]]>Table4[[#This Row],[Amt. Audit]],"over 100%",Table4[[#This Row],['# of 1''s]]/Table4[[#This Row],[Amt. Audit]])

or

=IF(Table4[[#This Row],['# of 1''s]]>Table4[[#This Row],[Amt. Audit]],1,Table4[[#This Row],['# of 1''s]]/Table4[[#This Row],[Amt. Audit]])

[TMS]

Please post a sample workbook.

Regards, TMS

[Pepe Le Mokko]

And which error?

[TMS]

@RHPCgergo: re edit - not necessarily, given the more recent examples.

[PunkDaddy]

Here is the example, thanks so much everyone!

Name # of 1's Amt. Audit % of 1's
Name1 1 2 50.00%
Name2 5 15 33.33%
Name3 15 16 93.75%