Forum Home
Press F1
Thread ID: 113045	2010-10-02 22:57:00	Need help with Excel 2007 IFTHEN function?	WalOne (4202)	Press F1

Post ID	Timestamp	Content	User
1141233	2010-10-02 22:57:00	I don't know how to achieve this: A1 = a $ amount B1 = date due for payment (e.g. 21/10, 21/11 etc) C1 = if B1 = 21/10 then copy A1 D1 = if B1 = 21/11 then copy A1 etc I need a simple sorting mechanism to achieve totals of amounts in C, then D. This is needed because the due dates may slide by a month, and this will give me a updated tally of what is due and when. Or am I over complicating it? Can someone help and come up with a solution?	WalOne (4202)
1141234	2010-10-03 00:18:00	A1 = a $ amount B1 = date due for payment (e.g. 21/10, 21/11 etc) C1 = if B1 = 21/10 then copy A1 D1 = if B1 = 21/11 then copy A1 I need a simple sorting mechanism to achieve totals of amounts in C, then D. Or am I over complicating it? Looks like it to me. I don't see any penalty or interest being added, so the amount due remains the same.	pctek (84)
1141235	2010-10-03 00:18:00	I used input tables and Goal seek together, to achieve similar a while back . Maybe try what if scenarios, using I think the 2 input table ( . revoledu . com/kardi/tutorial/What-If-Analysis/Data-Table-Two-Variables . htm" target="_blank">people . revoledu . com) (or try also the one input table ( . recipester . org/Recipe:Create_a_one-input_data_table_in_Excel_35402188" target="_blank">www . recipester . org)) - since there are two variables . You will have to create a formula first, to work with the input rows in the table dialog box, to generate a table of results . Goal seek ( . homeandlearn . co . uk/me/mes8p4 . html" target="_blank">www . homeandlearn . co . uk) might be able to do so as well (and can work in "reverse") - based on the same formula . Also depends how many data you are going to use, to make using the tables/goal seek worthwhile . Hard to explain but many tutorials online, e . g . Microsoft for 2 input tables . ( . microsoft . com/kb/282851" target="_blank">support . microsoft . com) But were a little tedious to set up, but worked well once various row data/variables were inserted in the table dialog boxes .	kahawai chaser (3545)
1141236	2010-10-03 00:28:00	This is only a small part of the answer, but you'll need to beware that your arguement regarding the date is not interpretted as a maths function to divide 21 by 10. Suggest you enclose the 21/10 within quotes ie if B1="21/10" then A1 Dunno if the rest of that will be interpretted correctly. Try it and see. An alternative might be to use if B1 like "21/10" then A1 (these answers are based on my experience with Access, not Excel)	Paul.Cov (425)
1141237	2010-10-03 01:08:00	I don't know how to achieve this: A1 = a $ amount B1 = date due for payment (e.g. 21/10, 21/11 etc) C1 = if B1 = 21/10 then copy A1 D1 = if B1 = 21/11 then copy A1 etc I need a simple sorting mechanism to achieve totals of amounts in C, then D. This is needed because the due dates may slide by a month, and this will give me a updated tally of what is due and when. Or am I over complicating it? Can someone help and come up with a solution? This works in OpenOffice and will be similar in excel Cell C1 contains =IF(B1="21/10",$A$1,"") Cell D1 contains =IF(B1="21/11",$A$1,"") Then just sum the columns C and D. This was done with the date as text so if in date format that will have to be changed.	mikebartnz (21)
1141238	2010-10-03 02:12:00	If I've read this right you are wanting to copy the date of it is the 21st of a month. This formula works - it copies the date if the first characters are 21 - it is set up to work with the cell (in this case B6) entered as a date =IF(LEFT(TEXT(B6,"dd/mm/yy"),2)="21",B6,"") Trev	TeejayR (4271)
1141239	2010-10-03 02:28:00	Thanks guys, mostly everyone were on the right track (i.e. able to read, and decipher what I meant) and got me sorted. The solution I settled on that worked was IF(logical test,[value if true],[value if false]) or (date text comparison,[$ amount to be copied over],[zero]) Excel seems to be much similar to OO from the example you gave, Mike, and yes I had to "anchor" some of the values ($A$1 etc). Cheers! :thumbs:	WalOne (4202)
1