IF function issue

[mbeyatli]

Hello all,
I'm new to excel and have been teaching myself slowly to help with some tasks I'm faced with at work.

I'm working on a table right now.
Column A has different types of carriers (A1=broadcast, A2=government, A3=Data, etc)
Column B is where i'd like to put the $ amount of rent that each different type of carrier pays

I tried =If(A1="broadcast", 350) ...and that works, but how do I create a formula where if A1= broadcast B1 will equal $350 or if A1=government then B1=250

I tried =IF(OR(F2="broadcast",350),OR(F2="government",250)) but it keeps returning TRUE.

I also tried doing =IF(or(and....but that didn't work either.

can anyone fix my formula and also explain why it works that way and not how I'm doing it?

thank you so much!!

[Sam Capricci]

Hi mbeyatli and welcome to the forum.
Please check the forum rules especially the one about titles.
http://www.excelforum.com/forum-rule...rum-rules.html
1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[easyexcelanswers]

try this formula in cell B1 and you should be able to drag it down

=IF(A1="broadcast", 350,IF(A1="government", 250,if(A1="data", 150, "no change")))

[mbeyatli]

one more question...i noticed some carriers have the same rent amount. How would i manipulate the formula to say if A1=government or utility, rent equals x

i tried =IF(F3="broadcast",1318,IF(F3="government",908,IF(OR(F3={"other","utility","tower company"},939),IF(F3={"data","wireless internet"},985,IF(F3="two way",346,IF(F3="paging",540))))))

the value for "other" comes up but when i have utility or Tower Company it says false.

[Sam Capricci]

But, I think this part IF(OR(F3={"other","utility","tower company"},939) is written wrong and should be =IF(OR(F3={"other","utility","tower company"}),939,"") instead.

[Sam Capricci]

As for the government or utility, that would be where you want to put the IF(OR(F3="government","Utility"),blah blah. Hopefully that helps.

[TMS]

Regards, TMS

[Sam Capricci]

from what you wrote it looks like you simply are approaching it wrong.
look at this for example...
=IF(A496="broadcast",350,IF(A496="government",250,450))
and thanks for changing the title BTW.

EDIT: I just used my own examples, yours of course will differ.

[mbeyatli]

wow I feel very dumb now haha. that was such a simple fix! thank you soooooooo much!!!!

[Sam Capricci]

You're welcome. Glad it helped. BTW, since you're new to the forum a little clean up that the moderators usually request is to mark the post as solved (if you haven't already).
You can do that using the thread tools at the top of the post.
AND, thanks for the bump to my reputation. Visit again!

[TMS]

There are several ways, depending on how long your list of carriers is:

=IF($A1="Broadcast",350,IF($A1="Government",250,IF($A1="Data",150,"not in list")))

or:

=IFERROR(CHOOSE(MATCH($A1,{"Broadcast","Government","Data"},0),350,250,150),"not in list")

or:

=IFERROR(VLOOKUP($A1,$H$1:$I$3,2,FALSE),"not in list")

For the last option, you need to make a list of the carriers and their rates ... in this case, cells H1:I3.

It has the advantage that is is more flexible and you can add entries if you need to.

If you make the list into a Named Range, say, CarrierTable, you can use:

=IFERROR(VLOOKUP($A1,CarrierTable,2,FALSE),"not in list")

And you can convert the list to a Table and then Excel will manage the Named Range so you can add entries. The most flexible option.

See the attached sample workbook.


Regards, TMS

[Sam Capricci]

I've not always had luck when using the {} in formulas (outside of array formulas). So that being said, maybe a list of things you want then a vlookup formula would work better for you?

[TMS]

=IF(F3="broadcast",1318,IF(F3="government",908,IF(OR(F3={"other","utility","tower company"}),939,IF(OR(F3={"data","wireless internet"}),985,IF(F3="two way",346,IF(F3="paging",540,"not in list"))))))

Regards, TMS