• 100% Satisfaction Guarantee
Richard, Computer Support Specialist
Category: Computer
Satisfied Customers: 40345
Experience:  Over 15 years of consulting to the IT industry
32989067
Richard is online now

# need a formula for exel If J=Y and IFD=0 then N=C can

### Customer Question

need a formula for exel
If J=Y
and IFD=0
then N=C
Submitted: 3 years ago.
Category: Computer
Expert:  Richard replied 3 years ago.
Thank you for your question, my name is XXXXX XXXXX I look forward to assisting you.

Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.

This is the formula to use

=if(and(J1="Y",IFD1=)),"C","")

You need to specify a Cell number as well so I added this as 1, and you cannot assign a value to another Cell via a formula, so you put the formula I gave you in N.

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

it does not seem to work

I need value n =value c that is 10.7

can you help thanks

Expert:  Richard replied 3 years ago.
ok, if you want the formula to be if N = C then 10.7

Then it is

if(N1=C1,10.7,"")

Is this what you want?

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.

sorry no

if J =Yand if d= o

then n = c

trying this on line 141

thank you

Expert:  Richard replied 3 years ago.
Are you doing this as a formula or via VBA Code Roger?

And what are the actual Cell references? eg: N1, J1 ect.

Customer: replied 3 years ago.

as formula

cell reference n141,J141,etc

Expert:  Richard replied 3 years ago.
ok, then to achieve

if J =Yand if d= o

then n = c

You use in N141

=if(and(J141=Y141,D141-0),C141,"")

That works for the rules you set.

If you want, give me the file and I can do this directly for you Roger.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.

=if(and(J141=Y141,D141-0),C141,"")

please see underline above as this does not give the correct answer

thank you

Expert:  Richard replied 3 years ago.
You say

if J =Y and if d= o

then n = c

So you now do not want the condition to be If J = Y?

And it is not

D141-0

It is

D141=0

That was a typo on my behalf.

Customer: replied 3 years ago.

hi

yes I need IF J=Y as sometimes it is not

I will upload the whole sheet if it is not too big

Expert:  Richard replied 3 years ago.
ok, then my formula works.

Look at what you underlined

=if(and(J141=Y141,D141-0),C141,"")

So if J141 = Y141

Please upload the sheet, it will be easier for me to do it directly on it.
Customer: replied 3 years ago.

Expert:  Richard replied 3 years ago.
Customer: replied 3 years ago.

have uploaded the file twice called march2013

have you got it

thank you roger

Expert:  Richard replied 3 years ago.
Roger,

Customer: replied 3 years ago.

have uploaded three times and got this

Mar2013.xls

• File ID: 345610

• File size: 267 KB

• Time to live: 7 days

Properties

Friendly file name:

Time to live:

days (1-90)

Description:

Expert:  Richard replied 3 years ago.
Thank you for the File ID

I have done this, the formula is in Column N

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

hi thanks got file back

sorry it is not correct yet

maybe I am unable to explain myself

line 141 should have10.7 in N as there is a Y in J

line 481 should have a blank as there is no y in J

Expert:  Richard replied 3 years ago.
ok, so if it equals the letter Y, and not the Column Y

I changed this for you and you can download it from www.wiksiend.com under File ID 432356

Let me know the results please
Customer: replied 3 years ago.

hi

thanks that works

now in the same spread sheet lone 573 M should be sum of d so formula shoud be =IF(AND(J573="y",D573>=0),D573,"") plus K= Y

hope this is clear as I need one formula to sort this column

thank you roger

Expert:  Richard replied 3 years ago.
No it is not clear sorry Roger.

What is it you now need me to do?

Change the current formula or a new formula?

Customer: replied 3 years ago.

change current formula in M to include an and or for line 573 which has its Y in K

will upload the amended sheet now

Expert:  Richard replied 3 years ago.
ok, upload the sheet and give me the new File ID please

Customer: replied 3 years ago.

file id Accounts\Billy\Mar2013 432356 amended.xls uploaded

Expert:  Richard replied 3 years ago.
ok, this is a new question though Roger, as I did your initial request which works.

I will do this as a followup, but can I ask please you rate me here before I do this new request?
Customer: replied 3 years ago.

have rated you excellent

Expert:  Richard replied 3 years ago.
Thanks

I have done it for you, you can download it from www.wikisend.com under File ID 857942

Is this what you required?
Customer: replied 3 years ago.

this should be the continuation of previous sheet 432356 which was half correct

Expert:  Richard replied 3 years ago.
No you wont, as no K cells have a Y

"Include an and or for line 573 which has its Y in K"
Customer: replied 3 years ago.

my copy has Y in k see line 573 will upload again as maybe you copy is fragmented

Expert:  Richard replied 3 years ago.
I do too, but you also said

=IF(AND(J573="y",D573>=0),D573,"") plus K= Y

So J573 needs a Y which it does not have.

Customer: replied 3 years ago.

formula for column M

k has Y in 573 to bottom of sheet with 5 exceptions that are J=Y

so we need what we have plus K=Y

IF(AND(J573="y",D573>=0),D573,"") and /or K= Y

it is difficult to explain

roger

Expert:  Richard replied 3 years ago.
It cannot be and or or Roger, it has to be one of the following

So in laymans terms.

J573="y",D573>=0

OR K = "Y"

Is this what you want?
Customer: replied 3 years ago.

I want

if J2=Y then M2=D2 (and or) if K=Y then M2=D2 all in one formula

Expert:  Richard replied 3 years ago.
It cannot be And or Or Roger it has to be one or the other,

Customer: replied 3 years ago.

have this sorted now

another sheet that needs sorting here are the facts

M £ requires value from D only when order number A=amz026,=amz204, amz202, amz203, BTF and J=Y

N = Euro value from D only when order number A

=amz028, amz403,amz302, amz402, amz304, amz404, and J=Y

O = \$ value from D when L = Y and order number =A =amz002, amz102, amz103, amz104, amz105, amz106, amz107,

amz108, amz109, amz110, amz111, amz112, and OP

P = yen value from D when L = Y and order number =A =amz249, amz503,

Q= £non requires value from c only when order number A=amz026,=amz204, amz202, amz203, BTF and J=Y

R = euronon Euro value from C only when order number A

=amz028, amz403,amz302, amz402, amz304, amz404, and J=Y

S=\$ non

O = \$ value from C when order number =A =amz002, amz102, amz103, amz104, amz105, amz106, amz107,

amz108, amz109, amz110, amz111, amz112, and OP and J=Y

P = yen value from C when order number =A =amz249, amz503, and J=Y

thank you roger

 £ Euro \$ YEN £non Euronon \$non Yennon
Expert:  Richard replied 3 years ago.
ok, but I will need a new question from this Roger.

Can you make it here please.

Expert:  Richard replied 3 years ago.
This is done Roger.

www.wikisend.com File ID 182682

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

entered the formulas but they show blank see this upload to you