Excel - if, then formula...

November 9, 2016 at 15:06:57
Specs: IOS, .../lots...
I'm trying (not at all successfully) to crate a simple formula in Excel 2011.

Scenario:

B2 = 50, C2 = Superstore-Sw

(The -Sw is a dash or hyphen preceding the Sw - not a minus sign)

I want E2 to equal (be a duplicate of) B2 if C2 = Superstore-Sw

I.e I want B2 to be copied to E2 if C2 has Superstore-Sw entered.

And of course the formula to continue down the page as the cell number(s) increase of course...

My effort thus far:

=if(c2="Superestore-Sw"), e2=b2)

Which I know isn't correct...

The logic of the formula is : if C2 = Superstore-Sw, then E2 = the value (a number) in B2

If Superstore-Sw is not present in C2 (i.e. some other text is entered there instead), then E2 should not duplicate B2 - and remain blank - no data/numbers entered.

And as I say, it must continue to operate with succeeding cells - without my need to intervene (by dragging the bottom right corner of E2 down the column)?

Help...!

And again - Takk...

message edited by trvlr


See More: Excel - if, then formula...

Report •

#1
November 9, 2016 at 16:44:24
Enter the following formula in E2 and drag it down as far as you think you'll need it.

=IF(C2="Superestore-Sw"),B2,""))

If you really do not want to drag it down I ask that you explain why. There are multiple options but we need more information.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 10, 2016 at 01:58:42
I have a working formula already for my own book keeping which requires the "drag down" routine. Having seen (somewhere, though can't recall where/when) similar formulae that do not require the drag down, I'm naturally curious to say the least, as to how it's achieved.

Takk for the formula. My interpretation of the logic in it is: if Superstore-Sw is present, the (the first option) B2 prevails, but if Superstore-Sw is'n't present then the next entry (option) prevails.

The " " indicating/symbolising no data to be entered, i.e. (E2) remains blank.

If one wished to include anothe option - i.e. besides Superstore-Sw being the initial condition, if I wanted (for example) Corner-store as well how would that be inlcuded?

The idea being that if either Superstore-Sw or Cornerstore was present in C2, then E2 would reflect the value of B2?

My reason for seeking a non drag down solution is simply to make the formula automatic. At present I manually copy data from B2 to E2, and to have it happen automatically is obviously "nicer". To "automate" the drag down (remove the need to) would be equally "nicer".


Report •

#3
November 10, 2016 at 12:51:58
Hi again:

=IF(C2="Superestore-Sw"),B2,""))

For some reason when I used the formula (cell numbers altered to those I actually will use) the formula wouldn't work - as above. Excel baulked at the -Sw part...

However.. if I omitted the bracket after Superstore-Sw - retaining the comma it works...

The formula then being:

=IF(C2="Superestore-Sw",B2,""))

The same formula works if it was SuperstoreSw as well...

Clearly I have much to learn and comprehend re' Excel formulae; it will be useful in the long run...

The reason for the formula is to remove the need to manually copy an entry from one cell (B2 in my example) to another (E2 in my example) after entering it in B2; the condition being the name of the store (my local supermarket actually). I want also to be able include an OR option too.

I mentioned that in my post #2...

So the formula will be to respond to an: If. C2 = Superstore - or C2 = CornerStore, then E2 = B2 in my example) or (blank entry if neither store is given in C2).


Report •

Related Solutions

#4
November 10, 2016 at 13:36:43
I'll cover the questions from both of your recent posts in one response.

First, the main one: the syntax of the formula.

I posted a typo, sorry about that. You are right, this will not work. The ^ indicates the syntax error(s).

=IF(C2="Superestore-Sw"),B2,""))
                       ^       ^

On the other hand, your formula will not work either.

=IF(C2="Superestore-Sw",B2,""))
                              ^

The correct syntax is:

=IF(C2="Superestore-Sw",B2,"")

i.e. only one set of parentheses is required.

re: if either Superstore-Sw or Cornerstore was present in C2, then E2 would reflect the value of B2?

=IF(OR(C2="Superestore-Sw",C2="Cornerstore"),B2,"")

re: The " " indicating/symbolising no data to be entered, i.e. (E2) remains blank.

I did not use " " in my formula, I used "". There is a big difference.

With a space between the quotes, Excel considers that to be a space. i.e. actual text.
With no space between the quotes, Excel considers that to be a "blank cell", even though it really isn't. (it's messy)

They will both appear to be a blank cell, but they are not. From a display standpoint, it may not matter, but certain Excel functions will treat a space differently than a blank cell.

re: "To "automate" the drag down (remove the need to) would be equally "nicer"."

The only solutions I can offer for an "automatic drag down" would be:

1 - A macro, although with a macro you wouldn't even need the formula. The macro could put the Bx value in Ex directly each time it was run. How to trigger the macro would be the next question, e.g. manually with a button or automatically after the entry in a cell, such as Cx. i.e As soon as you enter a value in Cx, the macro would run and the value in Bx is either placed in Ex or left blank, based on the contents of Cx. The macro could either put the formula in Ex or the bx value. your choice.

2 - Try double-clicking on the bottom right hand corner of the cell (ie on the box that you would otherwise drag). This should fill the formula down to the bottom of your data.

3 - Select a range of cells, type in your formula, and hold down Ctrl while you press Enter. This places the formula in all selected cells. As long as Cx is empty, Ex will be empty because Cx will not contain the your logical_test values, Superstore-Sw or Cornerstore.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 10, 2016 at 14:10:44
Thank you for the above.. I think I get the idea pretty well - at least for now... Who knows down the road.

My use of your formula (slightly modified...) did have the quotes as you showed them to be; my posting of it here was a typo - the space between them

I appreciate that an automatic "pull down as it were" isn't a bog standard routine; and using a macro doesn't seem to be worth the trials and tribulations?

The other two options do seem viable; and certainly I can/will give them a go...

Overall I guess the manual (of sorts) pull down is no great hardship... My mental processes tend to think along the lines of: if you can do this much and it's effectively automatic (the formula operation in this case), how to make it do even more...

Again many thanks for the time/effort you have given me. Muchly appreciated...

Likely I will be at some future time with another "how does one do this...?" post.

Cheers,

trvlr


Report •

#6
November 10, 2016 at 14:54:01
I assume you are aware that you can "pull down" the formula as far as you want long before you put any data in Columns B & C. As long as there is no data (or not the correct data) in Column C, the cell containing the formula will remain blank.

In other words, you don't have to pull the formula down only after you enter data in B/C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
November 10, 2016 at 15:25:34
Yes... As I say above I use a fairly complex formula (at least for me) to calculate a balance in my books in Excel. I use the pull down routine there, and as you suggest.

Report •

Ask Question