How JustAnswer Works:
• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
Ask The-PC-Guy Your Own Question
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1962
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# Need help making a formula that will assign a defined value

### Customer Question

Need help making a formula that will assign a defined value to cell depending on date in given cell. In addition, if date in cell does not match defined date I need to see "End" other side I need to see (K3, O)+P3.

Q2=month to date, H3 represents date of sale.

=IF(\$Q\$2>H3,"End",IF(MONTH(G3)=Q\$2, K3,0)+P3)

my formula is working for the first 12 columns in 2013 but when the date begins in 2014 it shows up in both the month for 2013 and 2014; doubling my amounts and providing inaccurate data.
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  Ryan-CTech replied 4 years ago.
Welcome To Just Answer!

Could you give me some data for the formula above? This way I can see what you see :)

Also, what is showing up and what it should be? Thanks
Customer: replied 4 years ago.

do you need me to upload the spread sheet?

Expert:  Ryan-CTech replied 4 years ago.
That would be great :)

You can upload it to www.wikisend.com

After upload copy the File ID number or Download link and paste it here.

Cheers
Customer: replied 4 years ago.

It is on its way, I highlighted the rows in green all the way accross. The two tabs that I am working on are recuring receivables and R1-A Joni Scott

Expert:  Ryan-CTech replied 4 years ago.
Ok I await your file.

Cheers
Customer: replied 4 years ago.

I have been working on this for several nights and work full time during the day.. Please forgive me I did not mean to be rude... Thank you very much for trying to help me with this project. I have been having a tough time with it.

Expert:  Ryan-CTech replied 4 years ago.
No worries,

I am here to help anyway I can. I know how frustrating it can be..trust me :)
Customer: replied 4 years ago.

Any update?

Expert:  Ryan-CTech replied 4 years ago.
You were supposed to be uploading me your file to the link I gave above.
Customer: replied 4 years ago.

I dud the upload twice?? I am not home from work yet but will be in about an 40 minutes. I will send as soon as I am in house.

Expert:  Ryan-CTech replied 4 years ago.
After you upload your file, Copy the File ID Number or Download link. And Paste it here.

Cheers :)
Customer: replied 4 years ago.

It is on its way, it looks like it went through and nothing was different than last time I uploaded a file. Please let me know asap if you got the file as I have been up since Sunday. I was waiting up this morning thinking you had the file.

I highlighted the rows in green all the way accross. The two tabs that I am working on are recuring receivables and R1-A Joni Scott.

Customer: replied 4 years ago.

It is on its way, it looks like it went through and nothing was different than last time I uploaded a file. Please let me know asap if you got the file as I have been up since Sunday. I was waiting up this morning thinking you had the file.

I highlighted the rows in green all the way across The two tabs that I am working on are recurring receivables and R1-A Joni Scott.

Customer: replied 4 years ago.

did it come through? In the past I have worked with another gentleman but I had to pay for it as I was under time constraints. He actually helped start this document. I am about to crash as I older then you and can't stay up 48 hrs straight ;-) enjoy your youth!! I will be back on tomorrow and if i have to pay to ensure that we can get this wrapped up that won't be an issue. Please let me know.

Elizabeth

Expert:  Ryan-CTech replied 4 years ago.
Its not coming through.

Are you uploading your file to www.wikisend.com? <<< Follow this link.

Then Upload your File. And give me the Download link or File ID Number like in this photo.

I'm not sure what you are doing to upload it but I am not getting it.
Customer: replied 4 years ago.

here is everything from the download page. I thought that was for me after we are done working on it.

572488

http://wikisend.com/download/572488/ResMan Sales and Commissions Ver 4.xlsx

ResMan Sales and Commissions Ver 4.xlsx

Expert:  Ryan-CTech replied 4 years ago.
Ok I got it sorted out :)

For some reason your Formula can't determine the Month Correctly on its own. So add quotes to the Month Result.

Example:
IF(\$Q\$3>H16,"End",IF(MONTH(G16)=6,K16,0)+P16)

Should be...
IF(\$Q\$3>H46,"End",IF(MONTH(G46)="6",K46,0)+P46)

Notice the Quotes around the 6.

I hope this helps :)

Cheers
Customer: replied 4 years ago.

sorry I am in meeting will that formula allow differentiation between march 2013 and march 2014?

Expert:  Ryan-CTech replied 4 years ago.
Yes because you have already included a > than Date in your formula. :)
Customer: replied 4 years ago.

when I put the formula in the cells that should have end now say value?

Expert:  Ryan-CTech replied 4 years ago.
Don't actually use the Formula's I gave you. All you need to do is add the Quotes around the Month number in each Formula in your File.

The Formulas were an Example.

Customer: replied 4 years ago.

That is what I did as your formula was relevant to that row only. It did not fix the issue. I went recurring receivables>row 3 and put in "2" around the number representing the month which made the amount clear where it was fine before ?

Expert:  Rachel-Mod replied 4 years ago.

Hello,

I’m a moderator for this topic. It seems the Professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you with your question, but sometimes finding the right professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!

Rachel

Customer: replied 4 years ago.

Thank you, I was tied up in meetings all day yesterday and into the evening. I am a little bit older and can't stay up all night 2 nights in a row. I am still needing help and have not resolved my problem.

D3 contants the date the contract was signed.

N2 contains the month the commission is to be paid. The formula below works if the contract reflects a date where the day is on the 1st; if the contract reflects a sign date of 3/15/13 for example the formula does not assign the value needed.

=IF(\$D3<N\$2,0, IF(AND(\$D3>=N\$2,\$D3<=N2),\$E3,0))

Expert:  Rachel-Mod replied 4 years ago.
Thank you for your patience. We will continue the search for a Professional for you.
Rachel
Expert:  The-PC-Guy replied 4 years ago.
Thanks for using JustAnswer. My name is Andrew, I will do whatever I can to answer your question

Attachments are only available to registered users.

Register Here

I am looking at your sheet now, I will let you know if I have any questions.
Customer: replied 4 years ago.

Thank you I am past deadline and have to complete this today no matter what and do not want to manually enter amounts in the columns in white on all those spreadsheets.

Thank you for picking up my issue

Expert:  The-PC-Guy replied 4 years ago.
I am happy to help, and believe me I will help you get it completed today. There is no reason why you should have to manually enter anything

based on this formula you wrote above.

=IF(\$D3<N\$2,0, IF(AND(\$D3>=N\$2,\$D3<=N2),\$E3,0))

that could never work because D3 cannot be >= and <= N2 at the same time

So essentially is this the value you want entered in N3

If the date in D3 is < the 1st of the month in N2, then it should be 0, otherwise it should be the value of E3?

Or does the date in D3 alter the value you want in N3, that is does it effect the value if the date is not on the 1st of the month, as you seamed to indicate above.

Is this correct?

Also is this all you need to have done?
Customer: replied 4 years ago.

Can I upload the file for you to view - in case I am not stating it correctly

Expert:  The-PC-Guy replied 4 years ago.
i already have the file, thanks, Perhaps you can explain the goal.
Customer: replied 4 years ago.

Columns N:AK need to reflect in each row below the number of units as determined by the contract date. So column N will show any units signed into a contract for the month(column header)

Expert:  The-PC-Guy replied 4 years ago.
Sorry Not following, the units are coming from Column E?

Am I supposed to multiply that by the value in Column I? To get a total \$ value?

And do partial months need to be calculated, That is if the month in Column N is 3/1/13
and the date in column D is 1/1/13 that would be 2 months correct? But if the date in column D were 1/15/13 that would be 1.5 months?

is this correct so far?
Customer: replied 4 years ago.

No I don't need to account for the \$.00 I need to pull over the number of units for that contract into columns N:AK. We calculate the commissions elsewhere. The number of units closed for each month determines the amount of the commission.

So I need to see the number of units in column E reflected in the appropriate month the contract was signed.

Expert:  The-PC-Guy replied 4 years ago.
ok, so all of the \$ signs in N:AK are just formatting, you are just interested in the number of units, and only if the month heading matches the contract date? and all of the others months should be blank or 0?
Customer: replied 4 years ago.

k, so all of the \$ signs in N:AK are just formatting, you are just interested in the number of units,> YES

and only if the month heading matches the contract date? NO- I need it to match Month, YEAR. When I date a contract for the 1st day of any of those columns the units carry over just fine, but if the contract starts on any other day of that month the units don't cary over.

and all of the others months should be blank or 0? YES

Expert:  The-PC-Guy replied 4 years ago.
ok, i got you now. Month and Year have to match
Customer: replied 4 years ago.

yes regardless of day

Expert:  The-PC-Guy replied 4 years ago.
here is the modified file.

http://ge.tt/9uypEod/v/0?c

click the link, then click to download

let me know if it works for you as it is now.
Customer: replied 4 years ago.

I am on the R1-A tab for Joni and I don't see any formula changes?

Expert:  The-PC-Guy replied 4 years ago.
the formula was put on the recurring receivables tab, did it need to go on multiple tabs?
Customer: replied 4 years ago.

oh no... we are trying to work on the yellow tabs only... I finished out the recurring on my own the night before. The tab tracks something different and explains why you were asking about amounts.. and i kept referencing units.

i thought it was in the previous emails. I am sorry- look at the joni tab and the need for month/day/year in columns N:AK

Expert:  The-PC-Guy replied 4 years ago.
ok i will make the changes
Expert:  The-PC-Guy replied 4 years ago.
sorry about the mixup

here is the corrected version

http://ge.tt/29h7fod/v/0?c
Expert:  The-PC-Guy replied 4 years ago.
did you get the last message, with the fixed sheet that has the tab you mentioned with the fixed formulas?