How do I use multiple functions?
I'm doing a spreadsheet and would like some help. I want the foumula to
comapre one cell and if it has the gift in it and the next cell over is below 40 then the value of the cell if over 40 then 40. Easy enough? no, I also want if the first cell has the word membership in it then the value of the next cell is the value. Example Cell 1 Amount claimed amount authorized gift 45 40 gift 35 35 membership 25 25 membership 70 70 Please help!!! |
How do I use multiple functions?
Hi Dan
With Gift etc. in column A, and value in column B enter in C2 =IF(A2="Membership",B2,IF(A2="GIFT",MIN(40,--(B2*ISNUMBER(B2))),"")) The ISNUMBER bit is to ensure that a blank value in B, with Gift in A doesn't get picked up as 40. Regards Roger Govier Dan L. wrote: I'm doing a spreadsheet and would like some help. I want the foumula to comapre one cell and if it has the gift in it and the next cell over is below 40 then the value of the cell if over 40 then 40. Easy enough? no, I also want if the first cell has the word membership in it then the value of the next cell is the value. Example Cell 1 Amount claimed amount authorized gift 45 40 gift 35 35 membership 25 25 membership 70 70 Please help!!! |
How do I use multiple functions?
Roger,
Thanks for the help. It worked , but let me add on to this. How do I get it to determine that if there is more than the word gift or membership in the cell. I have to describe it also in the same cell and I want it to calculate with the instance of gift or membership. Thanks again. Dan "Roger Govier" wrote: Hi Dan With Gift etc. in column A, and value in column B enter in C2 =IF(A2="Membership",B2,IF(A2="GIFT",MIN(40,--(B2*ISNUMBER(B2))),"")) The ISNUMBER bit is to ensure that a blank value in B, with Gift in A doesn't get picked up as 40. Regards Roger Govier Dan L. wrote: I'm doing a spreadsheet and would like some help. I want the foumula to comapre one cell and if it has the gift in it and the next cell over is below 40 then the value of the cell if over 40 then 40. Easy enough? no, I also want if the first cell has the word membership in it then the value of the next cell is the value. Example Cell 1 Amount claimed amount authorized gift 45 40 gift 35 35 membership 25 25 membership 70 70 Please help!!! |
How do I use multiple functions?
Hi Dan
Do you mean that A2 might contain "sports Club "Membership" or "Christmas Gift"? If so, try =IF(ISNUMBER(SEARCH(A2,"Membership")),B2,IF(ISNUMB ER(SEARCH(A2,"GIFT")),MIN(40,--(B2*ISNUMBER(B2))),"")) Note that Search is not case sensitive so Gift is treated the same as GIFT. If you did need to be specific on case, then use FIND in place of SEARCH. Regards Roger Govier Dan L. wrote: Roger, Thanks for the help. It worked , but let me add on to this. How do I get it to determine that if there is more than the word gift or membership in the cell. I have to describe it also in the same cell and I want it to calculate with the instance of gift or membership. Thanks again. Dan "Roger Govier" wrote: Hi Dan With Gift etc. in column A, and value in column B enter in C2 =IF(A2="Membership",B2,IF(A2="GIFT",MIN(40,--(B2*ISNUMBER(B2))),"")) The ISNUMBER bit is to ensure that a blank value in B, with Gift in A doesn't get picked up as 40. Regards Roger Govier Dan L. wrote: I'm doing a spreadsheet and would like some help. I want the foumula to comapre one cell and if it has the gift in it and the next cell over is below 40 then the value of the cell if over 40 then 40. Easy enough? no, I also want if the first cell has the word membership in it then the value of the next cell is the value. Example Cell 1 Amount claimed amount authorized gift 45 40 gift 35 35 membership 25 25 membership 70 70 Please help!!! |
All times are GMT +1. The time now is 10:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com