Formula terror

[Creolekitten]

Can anyone please help me?
I can't seem to figure out the following formula:
If C3 + D3 is >= 20 then the value of 20 should be place in F3 but if C3 + D3
is < or not equal to 20 then the percentage of the value of of C3 + D3 should
be placed in F3.
Is there such a way to write this formula in Excel. Any help would be
greatly appreciated.
Thank you

 

[Guest]

Hi
try this in F3

=IF(C3+D3>=20,20,C3/D3)
The last bit could be slightly different because you don't explain what the
percentage actually is !!

HTH
Michael M

 

[Creolekitten via OfficeKB.com]

Hello and thank you so much. I'm trying to find out what the percentage of C
& D combined would be of 20. I think I forgot some more of the formula also
because at the same time if C3 + D3 is < or not equal to 20 I need to
calculate what percentage of the sum of C3 & D3 would be of 20%. If the sum
is greater than or equal to 20 then the only value I want to print is 20
because that would max it but if less than 20 then what is the percentage of
the sum of C3 and D3.

Hi
try this in F3

=IF(C3+D3>=20,20,C3/D3)
The last bit could be slightly different because you don't explain what the
percentage actually is !!

HTH
Michael M

Can anyone please help me?
I can't seem to figure out the following formula:

[quoted text clipped - 4 lines]

greatly appreciated.
Thank you

 

[Fred Smith]

=20/(c3+d3)

is the percentage that c3+d3 is of 20.

However, now you have a display problem, because if c3+d3>20, you want the
number 20 displayed, but if it's less, you want a percentage displayed. A
possible solution is to display only percentages using the formula:

=if(c3+d3>=20,0.20,20/(c3+d3))

and format as percent.

If that's not what you want, give us specific examples of what you want
displayed in each circumstance.

There's always a solution.

--
Regards,
Fred

Hello and thank you so much. I'm trying to find out what the percentage of C
& D combined would be of 20. I think I forgot some more of the formula also
because at the same time if C3 + D3 is < or not equal to 20 I need to
calculate what percentage of the sum of C3 & D3 would be of 20%. If the sum
is greater than or equal to 20 then the only value I want to print is 20
because that would max it but if less than 20 then what is the percentage of
the sum of C3 and D3.

Hi
try this in F3

=IF(C3+D3>=20,20,C3/D3)
The last bit could be slightly different because you don't explain what the
percentage actually is !!

HTH
Michael M

Can anyone please help me?
I can't seem to figure out the following formula:

[quoted text clipped - 4 lines]

greatly appreciated.
Thank you

 

[David Biddulph]

=20/(c3+d3)

is the percentage that c3+d3 is of 20.

You may prefer (c3+d3)/20 rather than 20/(c3+d3) ?

However, now you have a display problem, because if c3+d3>20, you want the
number 20 displayed, but if it's less, you want a percentage displayed. A
possible solution is to display only percentages using the formula:

=if(c3+d3>=20,0.20,20/(c3+d3))

and format as percent.

If that's not what you want, give us specific examples of what you want
displayed in each circumstance.

There's always a solution.

Perhaps =MIN((c3+d3)/20,1) and format that as percentage, you'll get 100% as
the maximum when C3+D3>20. With Fred's solution [having inverted the
20/(c3+d3), see above], you'd get the same answer for a total of 4 as for
20, as both would show 20%

You could dispense with the formatting as percentage, and use
=IF(C5+D5>=20,20,(C5+D5)/0.2&"%")
but that would give a text answer in the percentage case, so couldn't be
used for further calculations.

 

[Creolekitten via OfficeKB.com]

Thank you for your help it's amazing how one little missing operand can cause
so much problems.
Bernie

Hello and thank you so much. I'm trying to find out what the percentage
of C [quoted text clipped - 7 lines]
of
the sum of C3 and D3.

=20/(c3+d3)

is the percentage that c3+d3 is of 20.

You may prefer (c3+d3)/20 rather than 20/(c3+d3) ?

However, now you have a display problem, because if c3+d3>20, you want the
number 20 displayed, but if it's less, you want a percentage displayed. A

[quoted text clipped - 8 lines]

There's always a solution.

Perhaps =MIN((c3+d3)/20,1) and format that as percentage, you'll get 100% as
the maximum when C3+D3>20. With Fred's solution [having inverted the
20/(c3+d3), see above], you'd get the same answer for a total of 4 as for
20, as both would show 20%

You could dispense with the formatting as percentage, and use
=IF(C5+D5>=20,20,(C5+D5)/0.2&"%")
but that would give a text answer in the percentage case, so couldn't be
used for further calculations.

 

[Creolekitten via OfficeKB.com]

Thank you for your help. Yes the percentage of c3 + d3 is of 20.
Bernie

=20/(c3+d3)

is the percentage that c3+d3 is of 20.

However, now you have a display problem, because if c3+d3>20, you want the
number 20 displayed, but if it's less, you want a percentage displayed. A
possible solution is to display only percentages using the formula:

=if(c3+d3>=20,0.20,20/(c3+d3))

and format as percent.

If that's not what you want, give us specific examples of what you want
displayed in each circumstance.

There's always a solution.

Hello and thank you so much. I'm trying to find out what the percentage of C
& D combined would be of 20. I think I forgot some more of the formula also

[quoted text clipped - 19 lines]