Please can someone help with an IF formula

[marcidee]

in C27 I have a drop down box that says
CONNECT MANAGEMENT FEE (10%) Yes
CONNECT MANAGEMENT FEE (10%) NO

I have this formula which works fine in Cell D27

=IF(RIGHT(C27,3)="YES",D28*10%,"")

(so if No D27 is empty, if Yes Calculates 10% of D28)

Now in D28 I would like:
If C27 = YES it calculates (D26*0.111109)+D26
If C27 = No (or C27 is empty) D28 is the same as D26
Thank you for your help
Marc

 

[Peter_SSs]

What am I not understanding about you data or requirement?
I have your formula from post #1 in D27

works fine with 'No', ie if No then D28 is the same as D26

OK, here is my formula in D28 with that scenario. I have your formula from post #6 in E28 for comparison

24 10 13.xlsm
	C	D	E
26		3
27	No
28		3	3
Sheet2 (2)

Cell Formulas
Range	Formula
D27	D27	=IF(RIGHT(C27,3)="YES",D28*10%,"")
D28	D28	=D26+D26*0.111109*OR(D27<>"",RIGHT(C27,3)="YES")
E28	E28	=IF(RIGHT(C27,3)="YES",(D26*0.111109)+D26,IF(D27<>"",(D26*0.111109)+D26,D26))

Cells with Data Validation
Cell	Allow	Criteria
C27	List	Yes,No

however if C27 is Yes, then D28 should be D26*0.111109

.. and here is my formula with that scenario. I still have your formula from post #6 in E28 for comparison

24 10 13.xlsm
	C	D	E
26		3
27	Yes	0.333333
28		3.333327	3.333327
Sheet2 (2)

Cell Formulas
Range	Formula
D27	D27	=IF(RIGHT(C27,3)="YES",D28*10%,"")
D28	D28	=D26+D26*0.111109*OR(D27<>"",RIGHT(C27,3)="YES")
E28	E28	=IF(RIGHT(C27,3)="YES",(D26*0.111109)+D26,IF(D27<>"",(D26*0.111109)+D26,D26))

Cells with Data Validation
Cell	Allow	Criteria
C27	List	Yes,No

Seems to me my formula and your formula from post #6 are producing the same results. What am I missing?

 

[marcidee]

What am I not understanding about you data or requirement?
I have your formula from post #1 in D27


OK, here is my formula in D28 with that scenario. I have your formula from post #6 in E28 for comparison

24 10 13.xlsm
	C	D	E
26		3
27	No
28		3	3
Sheet2 (2)

Cell Formulas
Range	Formula
D27	D27	=IF(RIGHT(C27,3)="YES",D28*10%,"")
D28	D28	=D26+D26*0.111109*OR(D27<>"",RIGHT(C27,3)="YES")
E28	E28	=IF(RIGHT(C27,3)="YES",(D26*0.111109)+D26,IF(D27<>"",(D26*0.111109)+D26,D26))

Cells with Data Validation
Cell	Allow	Criteria
C27	List	Yes,No

.. and here is my formula with that scenario. I still have your formula from post #6 in E28 for comparison

24 10 13.xlsm
	C	D	E
26		3
27	Yes	0.333333
28		3.333327	3.333327
Sheet2 (2)

Cell Formulas
Range	Formula
D27	D27	=IF(RIGHT(C27,3)="YES",D28*10%,"")
D28	D28	=D26+D26*0.111109*OR(D27<>"",RIGHT(C27,3)="YES")
E28	E28	=IF(RIGHT(C27,3)="YES",(D26*0.111109)+D26,IF(D27<>"",(D26*0.111109)+D26,D26))

Cells with Data Validation
Cell	Allow	Criteria
C27	List	Yes,No

Seems to me my formula and your formula from post #6 are producing the same results. What am I missing?

Thank you for helping
The formula you have put in E28 (which was the one I was using) does work fine
The formula you have put in D28 still produces the error
D27 is also fine

 

[Peter_SSs]

The formula you have put in D28 still produces the error

You can see that it does not produce an error for my sample data & in fact produces an identical result to your formula in E28 for both sets of sample data.

What is different about what I have and what you have?

So that I can understand, can you post a small sample with XL2BB like I have that shows my formula producing that error?

 

[marcidee]

Apologies for the delay - its taken me a while to work out how to install the XL2BB add on - the first version is the one that works - the second is using your formulas

INTERNAL	SUB TOTAL	£1,230.00		CONNECT MANAGEMENT FEE (10%) Yes
INTERNAL	TOTAL INC MARGIN FEE	£1,722.00		CONNECT MANAGEMENT FEE (10%) No
INTERNAL	CONNECT MANAGEMENT FEE (10%) Yes	191.33
CLIENT FACING	CLIENT TOTAL	1913.33

Your formula

INTERNAL	SUB TOTAL	£1,230.00		CONNECT MANAGEMENT FEE (10%) Yes
INTERNAL	TOTAL INC MARGIN FEE	£1,722.00		CONNECT MANAGEMENT FEE (10%) No
INTERNAL	CONNECT MANAGEMENT FEE (10%) Yes	191.33
CLIENT FACING	CLIENT TOTAL	0.00