If column E contains x - do this, if contains y - so that, etc

[mtaylor]

Hi - thanks for looking.

I have responses being inputted to a sheet and I want formulas run depending on the text in cells in Column E

The different responses are:

Double Sided, A4

Double Sided, A5/2 On 1pg

Double Sided

Single Sided

Double Sided

Single Sided

Then the formulas in column H are:

=((ROUNDUP(F2/2)*G2))

=((ROUNDUP(F3/4)*G3))

=((ROUNDUP(F4/2)*G4))

=G5*F5

=((ROUNDUP(F6/2)*G6))

=G7*F7

So for example:

If E3 says "Double Sided, A4" I want the formula "=((ROUNDUP(F2/2)*G2))" to be run etc

Hopefully this makes a little sense?

Thanks in advance

 

[jtakw]

Thank you,

I need all the formulas in one cell though, hence I was looking at the IFS function.

Where have I gone wrong here:

=IFS(E2="Double Sided, A4",((ROUNDUP((F2/2)*G2)),""),(E2="Double Sided, A5/2 On 1pg",((ROUNDUP(F2/4)*G2)),""),(E2="Double Sided",((ROUNDUP(F2/2)*G2)),""),(E2="Single Sided",(G2*F2),""))

That IS a formula in ONE cell, I just gave you 2 versions, use ONLY the one in H2, copied down.
My sample above in Post #6, H2 is just a different version of my I2 formula, you DON'T need Both, just Either one.

I Don't think you actually tried my formulas.

You Don't need a lengthy IFS.

Book3.xlsx
	E	F	G	H
1		#Original	#Copy	#Printouts
2	Double Sided, A4	2	20	20
3	Double Sided, A5/2 On 1pg	8	84	168
4	Double Sided	3	84	168
5	Single Sided	3	26	78
6	Double Sided	3	10	20
7	Single Sided	2	6	12
Sheet1045

Cell Formulas
Range	Formula
H2:H7	H2	=ROUNDUP(F2/MAX(SUM(COUNTIF(E2,{"*2 On 1*","*Double*"}))*2,1),0)*G2

 

[Alex Blakenburg]

@jtakw, I got a different result to you on row 6 Double Sided. I got 15 to your 20.
The formula in Post #1 results in 15 despite the image in post #1 showing 20.

 

[jtakw]

@jtakw, I got a different result to you on row 6 Double Sided. I got 15 to your 20.
The formula in Post #1 results in 15 despite the image in post #1 showing 20.

Don't know what result your formula would produce for row 6, I don't have IFS

But OP's formula for row 6 is: =((ROUNDUP(F6/2)*G6))

which is missing the "number digits" argument, assume should be 0

Will return 20.

Logic:

Double sided>original/2>roundup>*# of Copies
Yes>3/2=1.5>2*10=20

EDIT: looking at your formula, you're *G6 First, then ROUNDUP, but OP's incorrect formula Closed the ROUNDUP function Before *G6, the missing "number digits" argument is here:

=((ROUNDUP(F6/2)*G6))

2nd EDIT: If the ROUNDUP is performed After *G6, I don't even see a need for the ROUNDUP at all, for Any of the scenarios.

 

[jtakw]

Change F6 to 1, your formula will result 5, does that make sense?

1 Double sided original, 10 copies, 5 Total Prints???

Mine results 10.

Book3.xlsx
	E	F	G	H
1		#Original	#Copy	#Printouts
2	Double Sided, A4	2	20	20
3	Double Sided, A5/2 On 1pg	8	84	168
4	Double Sided	3	84	168
5	Single Sided	3	26	78
6	Double Sided	1	10	10
7	Single Sided	2	6	12
Sheet1045

Cell Formulas
Range	Formula
H2:H7	H2	=ROUNDUP(F2/MAX(SUM(COUNTIF(E2,{"*2 On 1*","*Double*"}))*2,1),0)*G2

 

[Alex Blakenburg]

Thanks @jtakw, I haven't logged in for the day yet but your logic makes it clear my current formula is incorrect. I will post a corrected "if" version later, in case the OP would prefer to stick with using an if statement.

 

[jtakw]

If I'm interpreting OP's logic incorrectly, and row 6 should be (double side 3 original = 15 prints, double side 1 original = 5 prints), then as I mentioned, the ROUNDUP is pointless.

And my formula will be even simpler:

Book3.xlsx
	E	F	G	H
1		#Original	#Copy	#Printouts
2	Double Sided, A4	2	20	20
3	Double Sided, A5/2 On 1pg	8	84	168
4	Double Sided	3	84	126
5	Single Sided	3	26	78
6	Double Sided	3	10	15
7	Double Sided	1	10	5
8	Single Sided	2	6	12
Sheet1045

Cell Formulas
Range	Formula
H2:H8	H2	=F2/MAX(SUM(COUNTIF(E2,{"*2 On 1*","*Double*"}))*2,1)*G2

 

[jtakw]

Thanks @jtakw, I haven't logged in for the day yet but your logic makes it clear my current formula is incorrect. I will post a corrected "if" version later, in case the OP would prefer to stick with using an if statement.

That may not be necessary, my last post above, I may have mis-interpreted OP's logic.
You would be correct, if.

Column E, printing on Double side
Column F, 3 originals
Column G, 10 copies
Column H, you would only need 15 sheets of paper, so # of prints=15

But, I guess, the ROUNDUP threw me off if this is the logic, my formula adjusted in Post # 16 if this is the case. (basically took out ROUNDUP)

 

[Alex Blakenburg]

@mtaylor, I believe that @jtakw has correctly analysed your intent and that my initial formula which you have marked as a solution is not giving you a correct result.
Assuming #Printouts represents the number of sheets of paper required, my formula is returning 15 pages for Row 6 Double Sided and it should be 20.
(an original of 3 pages, that doesn't share pages with additional copies requires 2 pages per original in Row 6 this means 2 pages x 10 copies = 20 pages not my 15)

You can either run with jtakw's solution or still using "IFS" this:

=IFS(E2="Double Sided, A5/2 On 1pg",ROUNDUP(F2/4,0)*G2,
         COUNTIF(E2, "Double Sided*"),ROUNDUP(F2/2,0)*G2,
         COUNTIF(E2,"Single Sided*"),G2*F2,
         TRUE,"")

Or Closer to the Original, just moving the rounding.

=IFS(E2="Double Sided, A4", ROUNDUP(F2/2,0)*G2,
         E2="Double Sided, A5/2 On 1pg",ROUNDUP(F2/4,0)*G2,
         E2="Double Sided",ROUNDUP(F2/2,0)*G2,
         E2="Single Sided",(G2*F2),
         TRUE,"")