Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan L.
 
Posts: n/a
Default 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!!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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!!!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan L.
 
Posts: n/a
Default 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!!!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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!!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change text within multiple functions Harv Excel Worksheet Functions 3 November 7th 05 07:24 PM
Functions across multiple worksheets starlight Excel Worksheet Functions 0 August 10th 05 05:10 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Excel Worksheet Functions 1 July 9th 05 03:05 AM
Multiple "IF" functions one one cell returned from different cells KMAPRO New Users to Excel 4 June 9th 05 11:38 PM
how do I use multiple nested functions? TeeJay Excel Worksheet Functions 3 February 20th 05 06:09 PM


All times are GMT +1. The time now is 11:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"