Data Cell Formula - cell = (a) unless total of cell is less than (b)

L

Lexie

Hi,
Wondering if someone can assist please...
We have a table that has the following calculation in it:

=SUM(B10*60+C10)/60*E7

Which is determining the cost of a phone call using minutes, seconds and rate per second.

What I need to be able to do is say that the above calculation is correct, unless the total is less than x amount (ie, .37), then that total should equal 37.

This is the current train of thought, which is obviously wrong....

=SUM(B10*60+C10)/60*E7+IF(B10=0,,+E6)+IF(B10=0,,+E5)+IF(B10<=14,,(B10-15)*E4)+IF(B10>=10,+E3,)

TIA


Submitted via EggHeadCafe - Software Developer Portal of Choice
Seamless Data Compression in .NET
http://www.eggheadcafe.com/tutorial...d-f4d9eaf13469/seamless-data-compression.aspx
 
R

Ron@Buy

Use a spare cell to enter the x amount, lets say F2, then:
=IF(SUM(B10*60+C10)/60*E7<F2,37,SUM(B10*60+C10)/60*E7)
You can then vary the 37 (in F2) without editing the formula.
Hope this helps
 
R

Roger Govier

Hi Lexie

Try
=MAX(0.37,SUM(B10*60+C10)/60*E7)

--
Regards
Roger Govier

Hi,
Wondering if someone can assist please...
We have a table that has the following calculation in it:

=SUM(B10*60+C10)/60*E7

Which is determining the cost of a phone call using minutes, seconds and
rate per second.

What I need to be able to do is say that the above calculation is correct,
unless the total is less than x amount (ie, .37), then that total should
equal 37.

This is the current train of thought, which is obviously wrong....

=SUM(B10*60+C10)/60*E7+IF(B10=0,,+E6)+IF(B10=0,,+E5)+IF(B10<=14,,(B10-15)*E4)+IF(B10>=10,+E3,)

TIA


Submitted via EggHeadCafe - Software Developer Portal of Choice
Seamless Data Compression in .NET
http://www.eggheadcafe.com/tutorial...d-f4d9eaf13469/seamless-data-compression.aspx

__________ Information from ESET Smart Security, version of virus
signature database 4752 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4752 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
D

Dave Peterson

And just removing that unnecessary =Sum() function:

=MAX(0.37,(B10*60+C10)/60*E7)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top