Help With Excel Formulas

 GoldLounger
 Posts: 3322
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thanks so much for that explanation. Not sure I'll retain it but the good news is I did understand what you explained.
Regards,
hlewton
hlewton

 GoldLounger
 Posts: 3322
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
Need help again and I hope my explanation is clear enough.
I am trying to create a formula and can’t get it right. I am trying to put the formula in cell K3 using the value in cell J3. However, the value in J3 can be a value of 1,2,4, or 5. Values of 4 or 5 are calculated by the formula J33 because the value in cell J3 should be 3 but is blank if it is OK and has a value of 3. This value can be changed by increments of 1 BUT only in a forward direction. In other words, the value could increase to 5 and then start over at 1. So, I tried to make a formula that says if J3 is less than 3, then J3 must first subtract 3 from 5 and then add whatever the number is in J3 to that value. So, if J3 has a value of 2 the steps to get to that value would require a move of 4 steps. Two examples of the formulas I tried are attached but I cannot get them to work as I need. What needs corrected to make this work? Thanks
I am trying to create a formula and can’t get it right. I am trying to put the formula in cell K3 using the value in cell J3. However, the value in J3 can be a value of 1,2,4, or 5. Values of 4 or 5 are calculated by the formula J33 because the value in cell J3 should be 3 but is blank if it is OK and has a value of 3. This value can be changed by increments of 1 BUT only in a forward direction. In other words, the value could increase to 5 and then start over at 1. So, I tried to make a formula that says if J3 is less than 3, then J3 must first subtract 3 from 5 and then add whatever the number is in J3 to that value. So, if J3 has a value of 2 the steps to get to that value would require a move of 4 steps. Two examples of the formulas I tried are attached but I cannot get them to work as I need. What needs corrected to make this work? Thanks
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton
hlewton

 5StarLounger
 Posts: 773
 Joined: 24 Jan 2010, 15:56

 Administrator
 Posts: 75279
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
You don't need so many parentheses:
=IF(J3<3,53+J3,J33)
or, somewhat shorter:
=J2+IF(J3<3,2,3)
=IF(J3<3,53+J3,J33)
or, somewhat shorter:
=J2+IF(J3<3,2,3)
Regards,
Hans
Hans

 GoldLounger
 Posts: 3322
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thank you. I really thought I had tried the first one above but apparently I didn't since your works great.
Regards,
hlewton
hlewton

 GoldLounger
 Posts: 3322
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
I used your first formula that is bold above and thought I could figure out how to make the K3 cell remain blank if nothing was in J3. I tried a few things one which was this =IF((J3<3,53+J3,J33),"") Why isn't that working for me. I looked at many of your formulas from the past that I saved just for this kind of thing and I still can't figure it out. Again, what correction do I have to make to your formula above to make the cell appear empty unless J3 has a value in it?
Regards,
hlewton
hlewton

 Administrator
 Posts: 75279
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
You have to add an extra IF to check whether J3 is blank:
=IF(J3="","",IF(J3<3,53+J3,J33))
You can also use the IFS function:
=IFS(J3="","",J3<3,53+J3,J3>=3,J33)
=IF(J3="","",IF(J3<3,53+J3,J33))
You can also use the IFS function:
=IFS(J3="","",J3<3,53+J3,J3>=3,J33)
Regards,
Hans
Hans

 GoldLounger
 Posts: 3322
 Joined: 24 Oct 2010, 23:39
 Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thank you. I thought I needed an extra IF but just didn't get it right. Strange thing is I can understand it AFTER you design it but can never seem to figure it out on my own. Thanks for your help.
Regards,
hlewton
hlewton