#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 683
Default excel formula

Does anyone know how to create a cell that will calculate whether the number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default excel formula

You account for anything less than 40 and anything greater than 40 but what
if the value IS 40?

I'll assume you want:

Less than or equal to 40 = 46
Greater than 40 = 69

=IF(COUNT(A1),IF(A140,69,46),"")

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Does anyone know how to create a cell that will calculate whether the
number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default excel formula

Something like this, I reckon:
In B2, copied down: =IF(A2="","",IF(A240,A2*64,A2*46))
Adapt to suit. Any good? hit the YES below
--
Max
Singapore
---
"Brian" wrote:
Does anyone know how to create a cell that will calculate whether the number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 464
Default excel formula

=IF(AND(NOT(ISBLANK(A1)),A1<40),46,IF(AND(A140,NO T(ISTEXT(A1))),69,0))

Format the result cell as Currency.

--
Regards
Dave Hawley
www.ozgrid.com
"Brian" wrote in message
...
Does anyone know how to create a cell that will calculate whether the
number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 211
Default excel formula

Dear Valko

Hi.
What is the function of COUNT(A1) in
=IF(COUNT(A1),IF(A140,69,46),"")

According to your explanation, it accounts for entries equal to 40, but I
can not figure out "count" functionality in this regard.
Your note is highly appreciated.

--
Rasoul Khoshravan Azar



"T. Valko" wrote:

You account for anything less than 40 and anything greater than 40 but what
if the value IS 40?

I'll assume you want:

Less than or equal to 40 = 46
Greater than 40 = 69

=IF(COUNT(A1),IF(A140,69,46),"")

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Does anyone know how to create a cell that will calculate whether the
number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance



.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 211
Default excel formula

Back to my previous post, I think I got the answer. "count" checks to see if
the cell is empty or not and leaves the result empty if origin is empty or
not-number and enters second "if" otherwise.

That count usage was nice, but I couldn't understand its relation to equal
part to 40.
--
Rasoul Khoshravan Azar



"T. Valko" wrote:

You account for anything less than 40 and anything greater than 40 but what
if the value IS 40?

I'll assume you want:

Less than or equal to 40 = 46
Greater than 40 = 69

=IF(COUNT(A1),IF(A140,69,46),"")

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Does anyone know how to create a cell that will calculate whether the
number
in the orginal cell is above or below 40. If above 40 it will calculate at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance



.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default excel formula

Yes, you pretty much got it figured out.

COUNT tests that there is in fact only a number entered in the cell. Without
testing for a number it would be possible that if the cell contained a TEXT
entry then:

A140 would be TRUE and give an incorrect (or unexpected) result.

--
Biff
Microsoft Excel MVP


"Khoshravan" wrote in message
...
Back to my previous post, I think I got the answer. "count" checks to see
if
the cell is empty or not and leaves the result empty if origin is empty or
not-number and enters second "if" otherwise.

That count usage was nice, but I couldn't understand its relation to equal
part to 40.
--
Rasoul Khoshravan Azar



"T. Valko" wrote:

You account for anything less than 40 and anything greater than 40 but
what
if the value IS 40?

I'll assume you want:

Less than or equal to 40 = 46
Greater than 40 = 69

=IF(COUNT(A1),IF(A140,69,46),"")

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Does anyone know how to create a cell that will calculate whether the
number
in the orginal cell is above or below 40. If above 40 it will calculate
at
$69.00 for anything over if below it will calculate at $46.00.

Thanks in advance



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible blue[_2_] Excel Discussion (Misc queries) 2 July 11th 07 06:08 PM
Build excel formula using field values as text in the formula val kilbane Excel Worksheet Functions 2 April 18th 07 01:52 PM
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw DS Excel Worksheet Functions 4 October 7th 06 12:25 AM
Excel 2002 formula displayed not value formula option not checked Dean Excel Worksheet Functions 1 February 28th 06 03:31 PM
Converting an Excel formula to an Access query formula Adam Excel Discussion (Misc queries) 1 December 15th 04 04:38 AM


All times are GMT +1. The time now is 09:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"