• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

IF function in excel with a condition

If A1 is between 1 and 243 then use 244 to multiple 5.00 but when it's over 244 use the actual value in A1 to multiple it by 5.00

Trying to do an if statement in excel to automate a spreadsheet
0
plam125
Asked:
plam125
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
lisa_mcCommented:
try this
if A1 >= 1 and A1 <=243
then 244 x 5.00
else A1 x 5.00

Open in new window

0
 
jppintoCommented:
=IF(A1>244,A1*5,243*5)
0
 
plam125Director of ITAuthor Commented:
but when it's zero it's still multipying...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lisa_mcCommented:
should it not be

=IF(A1>244,A1*5,244*5)
0
 
jppintoCommented:
or like this:

=IF(AND(A1>=1,A1<=243),244*5,A1*5)
0
 
jppintoCommented:
To avoid the 0 condition:

=IF(A1<>0,IF(AND(A1>=1,A1<=243),244*5,A1*5),"")

jppinto
0
 
lisa_mcCommented:
sorry that was my typo above i used <=243

but it should be <=244 as it is above 244

change above to

=IF(A1<>0,IF(AND(A1>=1,A1<=244),244*5,A1*5),"")
0
 
lisa_mcCommented:
om my

just ignore my last comment my head is wrecked lol ;-0)
0
 
jppintoCommented:
Lisa: typo on your formula:

=IF(A1<>0,IF(AND(A1>=1,A1<=244),244*5,A1*5),"")

it should be:

=IF(A1<>0,IF(AND(A1>=1,A1<=243),244*5,A1*5),"")
0
 
plam125Director of ITAuthor Commented:
Thanks...that worked
0
 
lisa_mcCommented:
aw i know jppinto

i think i need a break lol
0
 
Saqib Husain, SyedEngineerCommented:
Although it is closed a minor simplification

=IF(A1<>0,IF(AND(A1>=1,A1<=243),244,A1)*5,"")
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now