Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Easy formula in excel


  • Please log in to reply

#1
Uruguay

Uruguay

    New Member

  • Member
  • Pip
  • 1 posts
Hi,

I need something like this:

ColumnA ColumnB

A 200
B 150
C 350
A 70
C 1000
B 480


Now I want in a ColumnC a formula to say:
If in columnA it says "A" and columnB is >100 then columnC = columnD*2.5, but if its "A" and columnB is >100 then columnC = columnD*3. If columnA says "B" and its between this and that, then columnD*5.

You get it? Its a combined formula with IF, or, and and too. Its easy but Im new at this! :whistling:
  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
that's no where near an easy formula heheheh

IF(logical_test,value_if_true,value_if_false) is the if statement you're wanting...

this may be an easier thing to help you with if you give us some actuall data instead of if this does this then what the heck?

doesn't need to be sensative...but i want to see exactly what you're doing...i'm going to doubt that you're basing your calculations on the first column having either an a or a b
  • 0

#3
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
actually...made a small breakthrough with what you've got there

=IF(A1="A",IF(B1>100,"atrueover","atrueUnder"),IF(A1="B",IF(B1>100,"btrueover","btrueunder"),"Neither"))

what this is doing is looking at cell A1 and asking if that cell equals "a" if it does it then checks cell B1 to see if it's over 100..if it is..it returns "atrueover", if it's not it returns "atrueunder") if A1 ISN'T = "a" then it asks if A1="b" if it is then it asks if b1 is > 100..if it is then it returns "btrueover" if it's not then it returns 'btrueunder"...if A1 is neither A nor B then it returns "neither"

to test this you can make a spreadsheet..within the spreadsheet you'd endup with the columns being
columnA | ColumnB | Column C |
A | 100 | <formula>|
B | 75 |
C | 1000 |

then you would drag the formula down from C1 to C3 which would make the data look like this

columnA | ColumnB | Column C |
A | 100 | atrueunder |
B | 75 | btrueunder |
C | 1000 | neither |

to make this work for you...you would need to make the "atrueunder" and the like into whatever calculations you want such as

=IF(A1="A",IF(B1>100,d1*2.5,d1*3),IF(A1="B",IF(B1>100,d1*5,D1*7),"Neither"))

wich would make your data look like this

columnA | ColumnB | Column C | Column D |
A | 100 | 3 | 1
B | 75 | 14 | 2
A | 25 | 9 | 3
B | 250 | 20 | 4
C | 1000 | neither | 5



if any of that makes sense
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP