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 online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,228
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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 online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,228
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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 online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,228
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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: 195
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 online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,228
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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: 195
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 09:03 AM.


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