Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-01-2018, 03:39 AM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default Insert cap figure instead of sum

now I have that working trying another formula



I have two cell working with previous formula with the 2 amounts capped.

I then want a total of the 2 amounts but if both cells have been capped I want the cap figure and not the SUM of the 2 cells.

I currently have this =SUM(T9,W9) and tried this =SUM(T9,W9,J9) but with this it caps all and doesn't add the 2 cells when needed.

any ideas please


Edit mod : this post is related to http://www.msofficeforums.com/excel/...l-formula.html

Last edited by Pecoflyer; 08-01-2018 at 07:29 AM.
Reply With Quote
  #2  
Old 08-01-2018, 07:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,161
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Which of the capped amounts would be the result?
Post a small sample sheet with some data and expected results ( click Go advanced - Manage attachments)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 08-02-2018, 12:50 AM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default

the capped figure would be the J9 cell
Reply With Quote
  #4  
Old 08-02-2018, 07:47 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,161
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Add an example as requested please
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 08-06-2018, 03:02 AM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default

please help
Attached Files
File Type: xlsx MSExcel.xlsx (10.0 KB, 7 views)
Reply With Quote
  #6  
Old 08-06-2018, 06:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,161
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Perhaps =MIN(L7+O7,C7) ?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #7  
Old 08-09-2018, 12:48 PM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default

This works OK but if a cell in the calculation L7+O7 is blank it returns #value??

I still want it to return the value of cell C7

Last edited by Pecoflyer; 08-10-2018 at 08:11 AM.
Reply With Quote
  #8  
Old 08-09-2018, 01:56 PM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 192
NBVC is on a distinguished road
Default

What about?


=MIN(C7,SUM(L7,O7))
Reply With Quote
  #9  
Old 08-10-2018, 02:25 AM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default

NBVC Brilliant - works well thank you
Reply With Quote
  #10  
Old 08-10-2018, 08:11 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,161
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Quote:
Originally Posted by breen77 View Post
This works OK but if a cell in the calculation L7+O7 is blank it returns #value??

I still want it to return the value of cell C7


If it returns a VALUE error, it is not blank but contains the empty text string ""
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #11  
Old 08-28-2018, 03:25 AM
breen77 breen77 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Jul 2016
Posts: 22
breen77 is on a distinguished road
Default

Looking some help.

I am looking column N to be Column J * M, if more than column B enter column B's value minus column L. In red what is should be.


ABCDEFGHIJKLMNOP30/01/201850.00YES11.1yes06/03/201822/05/201850.001.002626.005656.00 (24.00)8250.00
Reply With Quote
  #12  
Old 08-28-2018, 07:45 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 192
NBVC is on a distinguished road
Default

Try:


=IF(J2*M2>B2,B2-L2,J2*M2)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Figure number cross-referencing mess; figure numbers not updating. seanspotatobusiness Word 25 08-06-2018 05:00 PM
VBA to find any nine-figure number Chayes Word VBA 2 05-19-2017 06:06 AM
Trying to figure out this code sharpied Word 0 07-27-2016 12:09 PM
Caption Order: Figure 4 Figure 3 Figure 2 golfarchitect13 Word 5 05-07-2014 07:15 PM
Convert "Figure", "Figure", "Figure" to "Figure1", "Figure2", etc. altjx Word 4 06-04-2013 05:29 AM


All times are GMT -7. The time now is 10:59 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft