Help With Excel Formulas

User avatar
hlewton
GoldLounger
Posts: 3322
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
GoldLounger
Posts: 3322
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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 J3-3 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
Formula.jpg
Formula1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

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

Re: Help With Excel Formulas

Post by rory »

Too many brackets. That would be:
=IF(J3<3,2+J3,J3-3)
Regards,
Rory

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

You don't need so many parentheses:

=IF(J3<3,5-3+J3,J3-3)

or, somewhat shorter:

=J2+IF(J3<3,2,-3)
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 3322
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

Thank you. I really thought I had tried the first one above but apparently I didn't since your works great.
Regards,
hlewton

User avatar
hlewton
GoldLounger
Posts: 3322
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
17 Mar 2022, 11:06
You don't need so many parentheses:

=IF(J3<3,5-3+J3,J3-3)

or, somewhat shorter:

=J2+IF(J3<3,2,-3)
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,5-3+J3,J3-3),"") 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

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

You have to add an extra IF to check whether J3 is blank:

=IF(J3="","",IF(J3<3,5-3+J3,J3-3))

You can also use the IFS function:

=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 3322
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

HansV wrote:
17 Mar 2022, 11:54
You have to add an extra IF to check whether J3 is blank:

=IF(J3="","",IF(J3<3,5-3+J3,J3-3))

You can also use the IFS function:

=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
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